从SQL一个表的字段中取出被逗号分割的数据

create table #t(id int,vc varchar(200))
insert #t select 1 ,'S98001,S95463,S27356'
insert #t select 2 ,'T19883,S56765,W34548'

方法一:
select row_number() over(order by (select 1)) as id
,c.value('.','varchar(50)') as list 
from #t a
cross apply(select  cast('<row>'+replace(vc,',','</row><row>')+'</row>' as xml) as xmlcode) C1
cross apply xmlcode.nodes('*') t(c)

id                   list
-------------------- --------------------------------------------------
1                    S98001
2                    S95463
3                    S27356
4                    T19883
5                    S56765
6                    W34548

(6 行受影响)

 

方法二:
 SELECT fs.a
 FROM #t t
 CROSS APPLY dbo.f_split(t.vc,',') fs

dbo.f_split为表值函数
--实现split功能 的函数
ALTER function [dbo].[f_split](@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
as 
begin
 declare @i int
 set @SourceSql=rtrim(ltrim(@SourceSql))
 set @i=charindex(@StrSeprate,@SourceSql)
 while @i>=1
 begin
  insert @temp values(left(@SourceSql,@i-1))
  set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
  set @i=charindex(@StrSeprate,@SourceSql)
 end
 if @SourceSql<>'\'
  insert @temp values(@SourceSql)
 return 
end