create table tb(id
int
,value varchar (
30
))
insert into tb values (
1
,
'
aa,bb
'
)
insert into tb values (
2
,
'
aaa,bbb,ccc
'
)
--
方法1.使用临时表完成
SELECT TOP
8000
id = IDENTITY(
int
,
1
,
1
) INTO # FROM syscolumns a, syscolumns b
SELECT A.id, value
= SUBSTRING (A.[value], B.id, CHARINDEX(
'
,
'
, A.[value] +
'
,
'
, B.id) -
B.id)
FROM tb A, # B
WHERE SUBSTRING (
'
,
'
+ A.[value], B.id,
1
) =
'
,
'
DROP TABLE #
--
方法2.如果数据量小，可不使用临时表
select
a.id , value = substring (a.value , b.number , charindex(
'
,
'
, a.value +
'
,
'
, b.number) -
b.number)
from
tb a join master..spt_values b
on b.type
=
'
p
'
and b.number between
1
and len(a.value)
where
substring (
'
,
'
+ a.value , b.number ,
1
) =
'
,
'
--
2
. 新的解决方法(sql server
2005
)
create table tb(id
int
,value varchar (
30
))
insert into tb values (
1
,
'
aa,bb
'
)
insert into tb values (
2
,
'
aaa,bbb,ccc
'
)
--
方法1.使用xml完成
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)
--
方法2.使用CTE完成
; with tt
as
(
select
id,[value]= cast ( left ([value],charindex(
'
,
'
,[value]+
'
,
'
)-
1
)
as
nvarchar(
100
)),Split= cast (stuff([value]+
'
,
'
,
1
,charindex(
'
,
'
,[value]+
'
,
'
),
''
)
as
nvarchar(
100
))
from
tb
union all
select
id,[value]= cast ( left (Split,charindex(
'
,
'
,Split)-
1
)
as
nvarchar(
100
)),Split= cast (stuff(Split,
1
,charindex(
'
,
'
,Split),
''
)
as
nvarchar(
100
))
from
tt
where
split>
''
select
id,[value]
from
tt order by id option (MAXRECURSION
0
)
