--方法1
SELECT name,V FROM
(SELECT name,CAST('<V>'+REPLACE(leibie,',','</V><V>')+'</V>' AS XML)VS FROM A)T1
CROSS APPLY(SELECT N.V.value('.','VARCHAR(100)')V FROM T1.VS.nodes('/V')N(V))T2
--方法2
Select
a.name,COl2=substring(a.leibie,b.number,charindex(',',a.leibie+',',b.number)-b.number)
from
A,master..spt_values b
where
b.type='p' and charindex(',',','+a.leibie,b.number)=b.number
更多方法,参考
http://bbs.csdn.net/topics/310219852
SELECT name,V FROM
(SELECT name,CAST('<V>'+REPLACE(leibie,',','</V><V>')+'</V>' AS XML)VS FROM A)T1
CROSS APPLY(SELECT N.V.value('.','VARCHAR(100)')V FROM T1.VS.nodes('/V')N(V))T2
--方法2
Select
a.name,COl2=substring(a.leibie,b.number,charindex(',',a.leibie+',',b.number)-b.number)
from
A,master..spt_values b
where
b.type='p' and charindex(',',','+a.leibie,b.number)=b.number
更多方法,参考
http://bbs.csdn.net/topics/310219852
create table A表(name varchar(20),leibie varchar(60))insert into A表
select 'A分店','保健食品,非药品,医疗器械,一类医疗器械' union all
select 'B分店','三类医疗器械,化学药制剂'
-- 结果暂存为临时表#a
select a.name,
substring(a.leibie,b.number,charindex(',',a.leibie+',',b.number)-b.number) 'leibie'
into #a
from A表 a,master..spt_values b
where b.type='P' and b.number between 1 and len(a.leibie)
and substring(','+a.leibie,b.number,1)=','-- 清空A表
truncate table A表-- 导入A表
insert into A表(name,leibie)
select name,leibie from #a
-- 结果
select * from A表
/*
name leibie
-------------------- ---------------------
A分店 保健食品
A分店 非药品
A分店 医疗器械
A分店 一类医疗器械
B分店 三类医疗器械
B分店 化学药制剂(6 row(s) affected)
*/-- 删除临时表#a
drop table #a