declare @t table([var] varchar(400)) insert into @t select '1,2,30,23,32,42,232,522,' insert into @t select '2,30,23,32,42,232,522,1,' insert into @t select '23,5,23,32,42,232,522,1,'declare @var varchar(400) update @t set @var=[var], @var=replace(','+@var,',2,',''), [var]=case left(@var,1) when ',' then stuff(@var,1,1,'') else @var end where charindex(',2,',','+var)>0select * from @t /* 130,23,32,42,232,522, 30,23,32,42,232,522,1, 23,5,23,32,42,232,522,1, */
--更新时加一个判断即可, libin_ftsafe也是这个意思 。update 表 set 列=case when left(replace(','+列,',2,',','),1)=',' then stuff(replace(','+列,',2,',','),1,1,'') else replace(','+列,',2,',',') end where charindex(',2,',','+列)>0
定义参数只是为了语句更直观一些,其实不定义参数,一条SQL足以实现: ------------------------------------------------------------------------------------------------------------------------ declare @t table([var] varchar(400)) insert into @t select '1,2,30,23,32,42,232,522,' insert into @t select '2,30,23,32,42,232,522,1,' insert into @t select '23,5,23,32,42,232,522,1,'update @t set [var]=case left(replace(','+[var],',2,',''),1) when ',' then stuff(replace(','+[var],',2,',''),1,1,'') else replace(','+[var],',2,','') end where charindex(',2,',','+[var])>0select * from @t
insert into @t select '1,2,30,23,32,42,232,522,'
insert into @t select '2,30,23,32,42,232,522,1,'
insert into @t select '23,5,23,32,42,232,522,1,'declare @var varchar(400)
update @t
set @var=[var],
@var=replace(','+@var,',2,',''),
[var]=case left(@var,1) when ',' then stuff(@var,1,1,'') else @var end
where
charindex(',2,',','+var)>0select * from @t
/*
130,23,32,42,232,522,
30,23,32,42,232,522,1,
23,5,23,32,42,232,522,1,
*/
libin_ftsafe(子陌红尘)的:过于复杂,而且要用过程才行,违背了初衷,不过还是多谢你们,分照给不误。这个问题是高难度,算了,我自己在程序中处理了。估计不能实现。
stuff(replace(','+列,',2,',','),1,1,'') else replace(','+列,',2,',',') end
where charindex(',2,',','+列)>0
------------------------------------------------------------------------------------------------------------------------
declare @t table([var] varchar(400))
insert into @t select '1,2,30,23,32,42,232,522,'
insert into @t select '2,30,23,32,42,232,522,1,'
insert into @t select '23,5,23,32,42,232,522,1,'update @t
set
[var]=case left(replace(','+[var],',2,',''),1) when ',' then stuff(replace(','+[var],',2,',''),1,1,'') else replace(','+[var],',2,','') end
where
charindex(',2,',','+[var])>0select * from @t