[原创]把","号分隔的字串转化成一列的Table
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
(
@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
QQ:273352165
evlon#126.com
转载请注明出处。