34,588
社区成员
发帖
与我相关
我的任务
分享
SELECT @splitlen=LEN(@split+'a')-2
这个地方为什么不直接 @splitlen=LEN(@split) ?
非要加个’a‘
--> 测试数据: @tb
declare @tb table (id varchar(20))
insert into @tb
select '0,0,0,0,0,0,0,0'
update @tb set id=stuff(id,5,1,'1')
select * from @tb
id
--------------------
0,0,1,0,0,0,0,0
(1 行受影响)
貼一段老大的
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
GO
select dbo.f_setstr('0,0,0,0,0,0,0,0',3,1,',')
/*
--------------------------
0,0,1,0,0,0,0,0
(1 個資料列受到影響)
*/
梁哥果然强大
create table a(sno varchar(50))
go
insert into a
select '0,0,0,0,0,0,0,0'
--1
DECLARE @i int;
SET @i = 3;
SELECT STUFF(B.x,1,1,'') AS sno
FROM (SELECT sno,CONVERT(xml,'<v>'+REPLACE(sno,',','</v><v>')+'</v>') AS xml_sno
FROM a) AS A
CROSS APPLY (
SELECT A.xml_sno.query('<v>
{
for $i in //v[position()<sql:variable("@i")]
return concat(",",string($i))
}
{concat(",","1")}
{
for $i in //v[position()>sql:variable("@i")]
return concat(",",string($i))
}
</v>').value('.','varchar(50)') AS x
) AS B;
drop table a
--测试结果:
/*
sno
--------------------------------------------------
0 ,0,1,0 ,0 ,0 ,0 ,0
*/