一个修改逗号前数字的函数，出现问题，请高人指点

Question

ALTER function [dbo].[Return_upstr](
@s nvarchar(max),--输入字符
@flag nvarchar(10),--分隔符
@n int,--取第n个分隔符与第n-1个分隔符前的字符
@r nvarchar(50) --替换值
)
RETURNS NVARCHAR(max)
as 
begin
    declare @s1 nvarchar(max),@i int,@j int,@k int
    select @s1=@flag+@s,@i=0,@j=0,@k=0
    while @i<@n
    begin
        set @k=@j
        set @j=@j+LEN(left(@s1,CHARINDEX(@flag,@s1)))
        set @s1=stuff(@s1,1,charindex(@flag,@s1),'')
        set @i=@i+1
    end
    if @n=1
    set @s=STUFF(@s,@j,CHARINDEX(@flag,@s)-1,@r)
    else
    set @s=STUFF(@s,@j,@j-@k-1,@r)
    return @s
end
select dbo.Return_upstr('10,0,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,',',',2,100)----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10,10030,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,(1 行受影响)结果变成了 10030 而且删去了一个逗号。。 不知道到底是什么原因。。

Answer 1

create function [dbo].[Return_upstr](
@s nvarchar(4000),--输入字符
@flag nvarchar(10),--分隔符
@n int,--取第n个分隔符与第n-1个分隔符前的字符
@r nvarchar(50) --替换值
)
RETURNS NVARCHAR(4000)
as 
begin
    declare @s1 nvarchar(4000),@i int,@j int,@k int
    select @s1=@flag+@s,@i=0,@j=0,@k=0
    while @i<@n
    begin
        set @k=@j
        set @j=@j+LEN(left(@s1,CHARINDEX(@flag,@s1)))
        set @s1=stuff(@s1,1,charindex(@flag,@s1),'')
        set @i=@i+1
    end
    if @n=1
    set @s=STUFF(@s,@j,CHARINDEX(@flag,@s)-1,@r)
    else
    set @s=STUFF(@s,@j,@j-@k-2,@r)
    return @s
end
go

Answer 2

alter function [dbo].[Return_upstr](
@s nvarchar(4000),--输入字符
@flag nvarchar(10),--分隔符
@n int,--取第n个分隔符与第n-1个分隔符前的字符
@r nvarchar(50) --替换值
)
RETURNS NVARCHAR(4000)
as 
begin
    declare @s1 nvarchar(4000),@i int,@j int,@k int
    select @s1=@flag+@s,@i=0,@j=0,@k=0
    while @i<@n
    begin
        set @k=@j
        set @j=@j+LEN(left(@s1,CHARINDEX(@flag,@s1)))
        set @s1=stuff(@s1,1,charindex(@flag,@s1),'')
        set @i=@i+1
    end
    if @n=1
    set @s=STUFF(@s,@j,CHARINDEX(@flag,@s)-1,@r)
    else
    set @s=STUFF(@s,@j,@j-@k-2,@r)
    return @s
end
select dbo.Return_upstr('10,0,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,',',',2,100)

Answer 3

--一楼和二楼如果拿下面测试就会有问题：
select dbo.Return_upstr1('10,0,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,',',',10,100)
--结果
--10,0,30,40,50,60,70,80,90,10000,110,120,130,140,150,160,170,180,190,200,210,220,230,240,

Answer 4

--那就重写个吧
if object_id('dbo.Return_upstr') is not null 
drop function dbo.Return_upstr
go
create function dbo.Return_upstr(@str nvarchar(4000),
@flag nvarchar(10),
@pos int,
@replace nvarchar(50) 
)
returns nvarchar(4000)
as
begin
declare @return nvarchar(4000)
declare @ret nvarchar(4000),@i int
select @str = @str+@flag,@i = 1
while charindex(@flag,@str) > 0 
begin
select @ret = substring(@str,1,charindex(@flag,@str)-1)
if @i = @pos
begin
select @ret = @replace
end
select @return = isnull(@return+@flag,'')+@ret
select @str = stuff(@str,1,charindex(@flag,@str),'')
select @i = @i + 1
end
return @return
end
select dbo.Return_upstr('10,0,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,',',',2,100)----------------------------------------------10,100,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,

Answer 5

把
set @s=STUFF(@s,@j,@j-@k-1,@r)改成set @s=STUFF(@s,@j,@j-@k-2,@r)
试一试？

Answer 6

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_SetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_SetStr]
GO--分段截取函数
CREATE FUNCTION dbo.f_SetStr(
@s varchar(8000),      --包含数据项的字符串
@pos int,             --要更新的数据项的段
@value varchar(100),   --更新后的值
@split varchar(10)     --数据分隔符
)RETURNS varchar(8000)
AS
BEGIN
DECLARE @splitlen int,@p1 int,@p2 int
SELECT @splitlen=LEN(@split+'a')-2,
@p1=1,
@p2=CHARINDEX(@split,@s+@split)
WHILE @pos>1 AND @p1<=@p2
SELECT @pos=@pos-1,
@p1=@p2+@splitlen+1,
@p2=CHARINDEX(@split,@s+@split,@p1)
RETURN(CASE
WHEN @p1<@p2 THEN STUFF(@s,@p1,@p2-@p1,@value)
WHEN @p2>LEN(@s) THEN @s+@value
WHEN @p2=@p1 THEN STUFF(@s,@p1,0,@value) 
ELSE @s END)
END
GOselect dbo.f_SetStr('10,0,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,',2,100,',')
/*
10,100,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,(1 個資料列受到影響)*/