declare @s varchar(1000) set @s='1,2,3,4,5,6,7' declare @s1 varchar(1000) set @s1=right(replace(','+@s,',',''' as S union select '''),len(replace(','+@s,',',''' as S union select '''))-12)+'''' --print @s1 exec(@s1)
DECLARE @STR VARCHAR(1000) SET @STR='1,2,3,4,5,6,7 ' SET @STR='SELECT '+REPLACE(@STR,',',' UNION ALL SELECT ')EXEC(@STR) /* 1 2 3 4 5 6 7 */
create function dbo.fn_split ( @inputstr varchar(8000), @seprator varchar(10) ) returns @temp table (a varchar(200)) as begin declare @i int set @inputstr = rtrim(ltrim(@inputstr)) set @i = charindex(@seprator, @inputstr) while @i >= 1 begin insert @temp values(left(@inputstr, @i - 1)) set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) set @i = charindex(@seprator, @inputstr) end if @inputstr <> '\' insert @temp values(@inputstr) return end go --调用 declare @s varchar(1000) set @s='1,2,3,4,5,6,7 ' select * from dbo.fn_split(@s,',') drop function dbo.fn_split
set @s='1,2,3,4,5,6,7'
declare @s1 varchar(1000)
set @s1=right(replace(','+@s,',',''' as S union select '''),len(replace(','+@s,',',''' as S union select '''))-12)+''''
--print @s1
exec(@s1)
SET @STR='1,2,3,4,5,6,7 '
SET @STR='SELECT '+REPLACE(@STR,',',' UNION ALL SELECT ')EXEC(@STR)
/*
1
2
3
4
5
6
7
*/
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
go
--调用
declare @s varchar(1000)
set @s='1,2,3,4,5,6,7 '
select * from dbo.fn_split(@s,',')
drop function dbo.fn_split