34,594
社区成员
发帖
与我相关
我的任务
分享
if object_id('[tb]') is not null drop table [tb]
go
create table tb(id int,value varchar(30))
insert into tb values(1,'a&a,b&b')
go
declare @sign varchar(36) set @sign=newid()
declare @t table(id int,value varchar(400))
insert into @t select id,replace(value,'&',@sign) from tb
select a.id, replace(b.value,@sign,'&') as value
from(
select id, [value] = convert(xml,'<root><v>' + replace([value], ',', '</v><v>') + '</v></root>') from @t
)a
outer apply(
select value = n.v.value('.', 'varchar(100)') from a.[value].nodes('/root/v') n(v)
)b
drop table tb
/*
id value
----------- ---------
1 a&a
1 b&b
*/
SELECT @@VERSION
/*Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64>
(Build 7601: Service Pack 1) (Hypervisor) */
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(V)
)B
/*
id value
1 aa
1 bb
2 aaa
2 bbb
2 ccc*/
if object_id('[tb]') is not null drop table [tb]
go
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)B
DROP TABLE tb
create table tb(id int,value varchar(30))
insert into tb values(1,'a&a,b&b')
原方法通过
新方法错误