将文本拆分成临时表

将文本 按 分隔符 拆分成临时表

 

/*
*************************************************************
名    称: fn_split_text

功能简介: [将文本 按 分隔符 拆分成临时表]

参数简介: [@text] 需拆分的文本
    返回: 表:    subcolstr :拆分的字符串

特别说明:  
   select * from fn_split_text('XXXX,YYYY,DDDD')
*************************************************************
*/
if OBJECT_ID('fn_split_text')>0
    drop function fn_split_text
GO
create function dbo.fn_split_text(@text nvarchar(max))
returns @returntable table(subcolstr varchar(200))
as  
begin
    --------------------------定义变量-----------------------------------------------------------------------------
    declare @commastr varchar(max),@splitstr varchar(max),@splitlen int,@length int
    declare @splitChar char(1)
    --------------------------赋初值-------------------------------------------------------------------------------
    select  @splitChar = ','

    -----------------------------------------------------------------------------------------------------
    select @splitstr=@text,
        @splitlen=charindex(@splitChar,@splitstr),
        @length=datalength(@splitstr)

    while @length>0
    begin
    --------------------------------------------------------------------------------------
        if     @splitlen=0
                   set @splitlen=@length+1
   
        set    @commastr =@splitstr
        insert @returntable (subcolstr) values(substring(@commastr,1,@splitlen-1))
        select @splitstr = substring(@commastr,@splitlen+1,@length),
                   @length=datalength(@splitstr),
                   @splitlen=charindex(@splitChar,@splitstr)
   --------------------------------------------------------------------------------------
    end
        

return
end

GO