Sql字符串分组Split函数的两种兑现方法

????? 在给文章加自定义标签时，需要在存储过程中对输入的字符串按照“,”字符分割成一个字符数组。但是Sql中没有实现字符串分组的Split方法。因此就需要编写一个自定义的Split函数。我首先是使用表值函数的方法实现的字符串分组，但是在使用中感觉不是很方便。后来又在网上找到了一种使用两个标量函数，其中一个函数首先返回分割后字符数组的长度，另一个函数依次返回每个分割出的字符串。然后使用循环依次获取分割的字符。

表值函数实现Split方法

代码

?Create?FUNCTION?[dbo].[SplitToTable]
?(
?????@SplitString?nvarchar(max),
?????@Separator?nvarchar(10)='?'
?)
?RETURNS?@SplitStringsTable?TABLE
?(
?[id]?int?identity(1,1),
?[value]?nvarchar(max)
?)
?AS
?BEGIN
?????DECLARE?@CurrentIndex?int;
?????DECLARE?@NextIndex?int;
?????DECLARE?@ReturnText?nvarchar(max);
?????SELECT?@CurrentIndex=1;
?????WHILE(@CurrentIndex<=len(@SplitString))
?????????BEGIN
?????????????SELECT?@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;
?????????????END
?????RETURN;
?END

?

select * FROm dbo.SplitToTable('111,b2222,323232,32d,e,323232f,g3222', ',')

结果为

id????????? value
----------- ---------------------------------------
1?????????? 111
2?????????? b2222
3?????????? 323232
4?????????? 32d
5?????????? e
6?????????? 323232f
7?????????? g3222

(7 行受影响)

?

使用循环的方法

首先GetSplitLength函数返回分割后的字符数组的长度。

?

代码

?Create?function?[dbo].[GetSplitLength]
?(
??@String?nvarchar(max),??--要分割的字符串
??@Split?nvarchar(10)??--分隔符号
?)
?returns?int
?as
?begin
??declare?@location?int
??declare?@start?int
??declare?@length?int
?
??set?@String=ltrim(rtrim(@String))
??set?@location=charindex(@split,@String)
??set?@length=1
??while?@location<>0
??begin
????set?@start=@location+1
????set?@location=charindex(@split,@String,@start)
????set?@length=@length+1
??end
??return?@length
?end

?

?

select dbo.GetSplitLength('111,b2222,323232,32d,e,323232f,g3222',',')

结果为7。

?

GetSplitOfIndex函数是按顺序分别获取分割后的字符串。

?

代码

?ALTER?function?[dbo].[GetSplitOfIndex]
?(
??@String?nvarchar(max),??--要分割的字符串
??@split?nvarchar(10),??--分隔符号
??@index?int?--取第几个元素
?)
?returns?nvarchar(1024)
?as
?begin
??declare?@location?int
??declare?@start?int
??declare?@next?int
??declare?@seed?int
?
??set?@String=ltrim(rtrim(@String))
??set?@start=1
??set?@next=1
??set?@seed=len(@split)
??
??set?@location=charindex(@split,@String)
??while?@location<>0?and?@index>@next
??begin
????set?@start=@location+@seed
????set?@location=charindex(@split,@String,@start)
????set?@next=@next+1
??end
??if?@location?=0?select?@location?=len(@String)+1?
??
??return?substring(@String,@start,@location-@start)
?end

?

?

select dbo.GetSplitOfIndex('111,b2222,323232,32d,e,323232f,g3222',',', 3)

结果323232。

?

?

代码

?DECLARE?@Tags?nvarchar(max);
?SELECT?@Tags='111,b2222,323232,32d,e,323232f,g3222';
?DECLARE?@Tag?nvarchar(1000)
?DECLARE?@next?int;
?set?@next=1
?
?DECLARE?@Length?int;
?SELECT?@Length=dbo.GetSplitLength(@Tags,',')

?while?@next<=@Length
?begin
?????SET?@Tag?=?left(dbo.GetSplitOfIndex(@Tags,',',@next),?16);
?????print?@Tag
?????SET?@Next=@Next+1;
?END

?

?

结果为：

111
b2222
323232
32d
e
323232f
g3222