create table #t (a int) declare @s varchar(100),@sql varchar(8000) set @s='1,2,3,4,5' select @sql='insert #t select '+REPLACE(@s,',',' union all select ') exec (@sql) select * from #t /* a ----------- 1 2 3 4 5(5 行受影响)*/ drop table #t
我现在有一字段值为:a,b,c 分隔符为逗号. 我现在想实现目地值为: 字段: id name 1 a 2 b 3 c 在SQL中咋实现呢? declare @str varchar(8000) set @str = 'a1,b1,c2,d1,e3,f5' set @str = 'select name='''+replace(@str,',',''''+' union all select ''')+'''' exec(@str)
create function f_split(@str varchar(8000),@StrSeprate varchar(10)) returns @temp table(a varchar(100)) as begin declare @i int set @str=rtrim(ltrim(@str)) set @i=charindex(@StrSeprate,@str) while @i>=1 begin insert @temp values(left(@str,@i-1)) set @str=substring(@str,@i+1,len(@str)-@i) --当然,这里您也可以改写为STUFF,可以自己试着改写一下 set @i=charindex(@StrSeprate,@str) end if @str<>'\' insert @temp values(@str) return end --用法:select * from dbo.f_split('A:B:C:D:E',':')
--这种方式2000用循环、临时表,借用master..spt_values charindex、动态sql 拼union all等方式 2005以上可用xml --try declare @str varchar(100) set @str='1,2,3,4,5'select b.v from (select cast('<r>' + replace(@str,',','</r><r>') + '</r>' as xml) x) a cross apply (select v=t.x.value('.','varchar(10)') from a.x.nodes('//r') as t(x) ) b
没有预定义的split函数,得自己写有一个. create function f_split(@str varchar(8000),@StrSeprate varchar(10)) returns @temp table(a varchar(100)) as begin declare @i int set @str=rtrim(ltrim(@str)) set @i=charindex(@StrSeprate,@str) while @i>=1 begin insert @temp values(left(@str,@i-1)) set @str=substring(@str,@i+1,len(@str)-@i) set @i=charindex(@StrSeprate,@str) end if @str<>'\' insert @temp values(@str) return end
declare @s varchar(100),@sql varchar(8000)
set @s='1,2,3,4,5'
select @sql='insert #t select '+REPLACE(@s,',',' union all select ')
exec (@sql)
select * from #t
/*
a
-----------
1
2
3
4
5(5 行受影响)*/
drop table #t
分隔符为逗号.
我现在想实现目地值为:
字段:
id name
1 a
2 b
3 c 在SQL中咋实现呢?
declare @str varchar(8000) set @str = 'a1,b1,c2,d1,e3,f5' set @str = 'select name='''+replace(@str,',',''''+' union all select ''')+'''' exec(@str)
returns @temp table(a varchar(100))
as
begin
declare @i int
set @str=rtrim(ltrim(@str))
set @i=charindex(@StrSeprate,@str)
while @i>=1
begin
insert @temp values(left(@str,@i-1))
set @str=substring(@str,@i+1,len(@str)-@i) --当然,这里您也可以改写为STUFF,可以自己试着改写一下
set @i=charindex(@StrSeprate,@str)
end
if @str<>'\'
insert @temp values(@str)
return
end
--用法:select * from dbo.f_split('A:B:C:D:E',':')
2005以上可用xml
--try
declare @str varchar(100)
set @str='1,2,3,4,5'select b.v from
(select cast('<r>' + replace(@str,',','</r><r>') + '</r>' as xml) x) a
cross apply
(select v=t.x.value('.','varchar(10)') from a.x.nodes('//r') as t(x) ) b
create function f_split(@str varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
as
begin
declare @i int
set @str=rtrim(ltrim(@str))
set @i=charindex(@StrSeprate,@str)
while @i>=1
begin
insert @temp values(left(@str,@i-1))
set @str=substring(@str,@i+1,len(@str)-@i)
set @i=charindex(@StrSeprate,@str)
end
if @str<>'\'
insert @temp values(@str)
return
end