SQL自定义函数split分隔字符串

本文主要是介绍SQL自定义函数split分隔字符串，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一、F_Split：分割字符串拆分为数据表

Create FUNCTION [dbo].[F_Split](@SplitString nvarchar(max),  --源字符串@Separator nvarchar(10)=' '  --分隔符号，默认为空格)RETURNS @SplitStringsTable TABLE  --输出的数据表([id] int identity(1,1),[value] nvarchar(max))ASBEGINDECLARE @CurrentIndex int;DECLARE @NextIndex int;DECLARE @ReturnText nvarchar(max);SELECT @CurrentIndex=1;WHILE(@CurrentIndex<=len(@SplitString))BEGINSELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);IF(@NextIndex=0 OR @NextIndex IS NULL)SELECT @NextIndex=len(@SplitString)+1;SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);SELECT @CurrentIndex=@NextIndex+1;ENDRETURN;END--使用示例
select * FROm dbo.F_Split('111,b2222,323232,32d,e,323232f,g3222', ',')

结果为

id          value

-------- ---------------------------------------

1           111

2           b2222

3           323232

4           32d

5           e

6           323232f

7           g3222

二、F_SplitLength：获取分割后的字符数组的长度

Create function [dbo].[F_SplitLength](@String nvarchar(max),  --要分割的字符串@Split nvarchar(10)  --分隔符号)returns intasbegindeclare @location intdeclare @start intdeclare @length intset @String=ltrim(rtrim(@String))set @location=charindex(@split,@String)set @length=1while @location<>0beginset @start=@location+1set @location=charindex(@split,@String,@start)set @length=@length+1endreturn @lengthend--调用示例
select dbo.F_SplitLength('111,b2222,323232,32d,e,323232f,g3222',',')

结果为7。

 

=========================================================================

三、F_SplitOfIndex：获取分割后特定索引的字符串

Create function [dbo].[F_SplitOfIndex](@String nvarchar(max),  --要分割的字符串@split nvarchar(10),  --分隔符号@index int --取第几个元素)returns nvarchar(1024)asbegindeclare @location intdeclare @start intdeclare @next intdeclare @seed intset @String=ltrim(rtrim(@String))set @start=1set @next=1set @seed=len(@split)   set @location=charindex(@split,@String)while @location<>0 and @index>@nextbeginset @start=@location+@seedset @location=charindex(@split,@String,@start)set @next=@next+1endif @location =0 select @location =len(@String)+1   return substring(@String,@start,@location-@start)end--使用示例
select dbo.F_SplitOfIndex('111,b2222,323232,32d,e,323232f,g3222',',', 3)

结果为323232。

 

转自：http://www.cnblogs.com/xiaofengfeng/archive/2012/06/01/2530930.html

这篇关于SQL自定义函数split分隔字符串的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---