[原创]把”,”号分隔的字串转化成一列的Table
2011年04月16日
⁄ 综合
⁄ 共 1226字 ⁄ 字号
小 中 大
alter function dbo.ufn_ConvertToIntList
(
@data varchar(7990)
)
returns @table Table(c int not null)
as
begin
declare @str varchar(8000),@iter varchar(50)
,@beginPos int, @endPos int
select @str= @data + ',', @beginPos = 1, @endPos = charindex(',', @str, @beginPos)
while(@endPos > 0)
begin
set @iter = SubString(@str,@beginPos ,@endPos - @beginPos )
if(@iter <> '')
begin
insert into @table(c) values(Convert(int,@iter))
end
select @beginPos = @endPos + 1, @endPos = charindex(',', @str, @beginPos)
end
return
end
go
declare @data varchar(8000)
,@i int
select @i = 1, @data ='1,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9,2,3,4,5,6,7,8,9'
while(@i < 20)
begin
select * from dbo.ufn_ConvertToIntList(@data)
set @i= @i +1
end