关闭

把用","分隔的字符串转化成Table的一列

374人阅读 评论(0) 收藏 举报
create 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
 
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:217433次
    • 积分:2914
    • 等级:
    • 排名:第12120名
    • 原创:15篇
    • 转载:274篇
    • 译文:0篇
    • 评论:20条
    最新评论