select col = substring(a.col, b.number , charindex(',' , a.col+ ',' , b.number) - b.number) from tb a join master..spt_values b on b.type='p' and b.number between 1 and len(a.col) where substring(',' + a.col, b.number , 1) = ','
create table #TT(col nvarchar(1000)) insert #TT select 'aa,bb,cc,dd,'select * from [dbo].StrFunc((select top 1 col from #TT),',')create function [dbo].StrFunc(@c varchar(2000),@split varchar(2)) --把字符串截開,生成一個單列的表 returns @t table(col varchar(200)) as begin declare @i as int set @i=1 while(charindex(@split,@c)<>0) begin insert @t select substring(@c,1,charindex(@split,@c)-1) set @c = stuff(@c,1,charindex(@split,@c),'') set @i=@i+1 end return end
declare @sql varchar(max) declare @s varchar(20) set @s='aa,bb,cc,dd,'set @sql='select '''+replace(@s,',',''' union all select ''')+'''' exec(@sql)
declare @sql varchar(1000) declare @s varchar(20) set @s='aa,bb,cc,dd,'set @sql='select '''+replace(@s,',',''' union all select ''')+'''' exec(@sql)
from tb a join master..spt_values b
on b.type='p' and b.number between 1 and len(a.col)
where substring(',' + a.col, b.number , 1) = ','
create table #TT(col nvarchar(1000))
insert #TT
select 'aa,bb,cc,dd,'select * from [dbo].StrFunc((select top 1 col from #TT),',')create function [dbo].StrFunc(@c varchar(2000),@split varchar(2)) --把字符串截開,生成一個單列的表
returns @t table(col varchar(200))
as
begin
declare @i as int
set @i=1
while(charindex(@split,@c)<>0)
begin
insert @t select substring(@c,1,charindex(@split,@c)-1)
set @c = stuff(@c,1,charindex(@split,@c),'')
set @i=@i+1
end
return
end
declare @s varchar(20)
set @s='aa,bb,cc,dd,'set @sql='select '''+replace(@s,',',''' union all select ''')+''''
exec(@sql)
declare @s varchar(20)
set @s='aa,bb,cc,dd,'set @sql='select '''+replace(@s,',',''' union all select ''')+''''
exec(@sql)