[原创]把","号分隔的字串转化成一列的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
posted @ 2006-04-27 13:52  阿牛  阅读(391)  评论(3编辑  收藏  举报