在给文章加自定义标签时，需要在存储过程中对输入的字符串按照“,”字符分割成一个字符数组。但是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
(
@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
(
@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
(
@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
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
