这样: --drop table t2create table t2(n varchar(20))insert into t2 values('5,6,9,5')select cast(cast(left(n,charindex(',',n)-1) as int) - 2 as varchar) + stuff(n,1,charindex(',',n)-1,'') from t2 /* 3,6,9,5 */
create table hn (x varchar(20))insert into hn select '6' union all select '5,6,9,5' -- 更新 with t as (select a.x, substring(a.x,b.number,charindex(',',a.x+',',b.number)-b.number)-3 'y' from hn a inner join master.dbo.spt_values b on b.type='P' and b.number between 1 and len(a.x) and substring(','+a.x,b.number,1)=',') update a set a.x=b.h from hn a inner join (select c.x, stuff((select ','+rtrim(d.y) from t d where d.x=c.x for xml path('')),1,1,'') 'h' from t c group by c.x ) b on a.x=b.x-- 结果 select * from hn/* x -------------------- 3 2,3,6,2(2 row(s) affected) */
DECLARE @XmlDocumentHandle int declare @XmlDocument nvarchar(4000) set @XmlDocument='5,6,9,5' set @XmlDocument=REPLACE(@XmlDocument,',','</item><item>') set @XmlDocument=N'<Root><item>'+@XmlDocument+'</item></Root>'select o.value('.','int') 'item' into #temp from (select cast(@XmlDocument as xml) 'x') t cross apply x.nodes('/Root/item') x(o) declare @s varchar(1000) set @s='' select @s=@s+','+cast(item-3 as varchar) from #temp select STUFF(@s,1,1,'') drop table #temp/* 2,3,6,2 */
--drop table t2create table t2(n varchar(20))insert into t2
values('5,6,9,5')select cast(cast(left(n,charindex(',',n)-1) as int) - 2 as varchar) +
stuff(n,1,charindex(',',n)-1,'')
from t2
/*
3,6,9,5
*/
create table hn
(x varchar(20))insert into hn
select '6' union all
select '5,6,9,5'
-- 更新
with t as
(select a.x,
substring(a.x,b.number,charindex(',',a.x+',',b.number)-b.number)-3 'y'
from hn a
inner join master.dbo.spt_values b
on b.type='P' and b.number between 1 and len(a.x)
and substring(','+a.x,b.number,1)=',')
update a
set a.x=b.h
from hn a
inner join
(select c.x,
stuff((select ','+rtrim(d.y) from t d
where d.x=c.x
for xml path('')),1,1,'') 'h'
from t c
group by c.x
) b on a.x=b.x-- 结果
select * from hn/*
x
--------------------
3
2,3,6,2(2 row(s) affected)
*/
declare @XmlDocument nvarchar(4000)
set @XmlDocument='5,6,9,5'
set @XmlDocument=REPLACE(@XmlDocument,',','</item><item>')
set @XmlDocument=N'<Root><item>'+@XmlDocument+'</item></Root>'select o.value('.','int') 'item'
into #temp
from (select cast(@XmlDocument as xml) 'x') t
cross apply x.nodes('/Root/item') x(o)
declare @s varchar(1000)
set @s=''
select @s=@s+','+cast(item-3 as varchar) from #temp
select STUFF(@s,1,1,'')
drop table #temp/*
2,3,6,2
*/