--> 测试数据:[aaa]
if object_id(
'[aaa]'
)
is
not
null
drop
table
[aaa]
go
create
table
[aaa]([aid]
int
,[
name
]
varchar
(14))
insert
[aaa]
select
1,
'王为,赵立,卫名'
union
all
select
2,
'张三,李四'
--------------开始查询--------------------------
select
[aid],
SUBSTRING
(a.
name
,number,CHARINDEX(
','
,a.
name
+
','
,number)-number)
as
name
from
[aaa] a,master..spt_values
where
number >=1
and
number<len(a.
name
)
and
type=
'p'
and
substring
(
','
+a.
name
,number,1)=
','
----------------结果----------------------------
/*
aid
name
----------- --------------
1 王为
1 赵立
1 卫名
2 张三
2 李四
*/
转载于:https://blog.51cto.com/marsng/1725198