修改后的代码如下:
CREATE
FUNCTION
fn_Splitor (
@array nvarchar ( 4000 ),
@separator char = ' , '
)
RETURNS @tbls TABLE (Item int )
as
BEGIN
if ( @array is null or len ( ltrim ( rtrim ( @array ))) = 0 )
return ;
DECLARE @item nvarchar ( 4000 )
DECLARE @cur int
DECLARE @pos int
DECLARE @len int -- 字符串的长度
set @cur = 1
SET @len = len ( @array )
WHILE ( @cur <= @len )
BEGIN
set @pos = CharIndex ( @separator , @array , @cur )
-- 如果有连续两个分隔符@separator,则跳过此次循环
if ( @pos = @cur )
begin
set @cur = @cur + 1
continue
end
if ( @pos > 0 )
begin
set @item = SUBSTRING ( @array , @cur , @pos - @cur )
set @cur = @pos + 1
end
else
begin
-- 循环已到了@array的末尾,在末尾并不包含分隔符@separator。
set @item = SUBSTRING ( @array , @cur , @len - @cur + 1 )
set @cur = @len + 1
end
INSERT INTO @tbls VALUES ( @item )
END
RETURN
end
GO
@array nvarchar ( 4000 ),
@separator char = ' , '
)
RETURNS @tbls TABLE (Item int )
as
BEGIN
if ( @array is null or len ( ltrim ( rtrim ( @array ))) = 0 )
return ;
DECLARE @item nvarchar ( 4000 )
DECLARE @cur int
DECLARE @pos int
DECLARE @len int -- 字符串的长度
set @cur = 1
SET @len = len ( @array )
WHILE ( @cur <= @len )
BEGIN
set @pos = CharIndex ( @separator , @array , @cur )
-- 如果有连续两个分隔符@separator,则跳过此次循环
if ( @pos = @cur )
begin
set @cur = @cur + 1
continue
end
if ( @pos > 0 )
begin
set @item = SUBSTRING ( @array , @cur , @pos - @cur )
set @cur = @pos + 1
end
else
begin
-- 循环已到了@array的末尾,在末尾并不包含分隔符@separator。
set @item = SUBSTRING ( @array , @cur , @len - @cur + 1 )
set @cur = @len + 1
end
INSERT INTO @tbls VALUES ( @item )
END
RETURN
end
GO