在SQL中参数值超过8000(TEXT类型)的处理方法,以"逗号"分隔的字符串

来源：互联网发布：局域网视频聊天软件编辑：程序博客网时间：2024/05/15 04:44

create   function dbo.sms_fn_split_text(@text text)
returns @returntable table(subcolstr varchar(200))
as
begin
         --------------------------定义变量-----------------------------------------------------------------------------
         declare @fieldlen bigint,@nbegin bigint,@nsubstrlen bigint,@nflag bigint,@flag int
declare @sourcestring varchar(8000),@commastr varchar(8000),@splitstr varchar(8000),@splitlen int,@length int
         declare @branch_type varchar(10),@agent_branch_id varchar(50),@count bigint

         --------------------------赋初值-------------------------------------------------------------------------------
         select    @fieldlen =0,@nbegin=0,@nsubstrlen=0,@nflag = 0,@flag=0
         select @fieldlen = datalength(@text)
/*---------------------------------------------------------------------
         判断字段的段是否大于7000
         如果大于7000则分多次放到varchar变量中进行分隔
----------------------------------------------------------------------*/
         if @fieldlen >7000
         begin
                 while @nbegin+1<
                 begin
                 --------------------------------------------------------------------------------------------------
                         if @fieldlen-@nbegin>7000
                         begin
                         --------------------------------------------------------------------------------
                                 set @nsubstrlen=7000
                                 set @flag = 1
                         --------------------------------------------------------------------------------
                         end
                         else
                         begin
                         --------------------------------------------------------------------------------
                                 set @nsubstrlen = @fieldlen - @nbegin
                                 set @flag =0
                         --------------------------------------------------------------------------------
                         end
                         select   @sourcestring=substring(@text,@nbegin+1,@nsubstrlen)

                         select   @splitstr = @sourcestring,
                                 @splitlen = charindex(',',@splitstr),
                                 @length    = datalength(@splitstr),
                                 @nflag = @splitlen


                         while @length>0
                         begin
                         ----------------------------------------------------------------------------------
                          if      @splitlen=0
                                 begin
                                 --------------------------------------------------------------------------
                                         if @length = 7000
                                         begin
                                         ------------------------------------------------------------------
                                                 delete @returntable
                                                 insert @returntable (subcolstr)   values(null)
                                                 goto end_label
                                         ------------------------------------------------------------------
                                         end
                                         if @flag=0
                                             set @nbegin = @nbegin + @length
                                         break
                                 --------------------------------------------------------------------------
                                 end
                                         set @nbegin = @nbegin + @nflag

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

                 if @splitstr<>''
                  insert @returntable (subcolstr) values(substring(@splitstr,1,@splitlen))
         end
/*---------------------------------------------------------------------
         如果小于7000
                 则再判断是否为NULL值
                         如果为NULL则在数据库中取相应的数据
                         否则放入到VARCHAR中进行分隔
----------------------------------------------------------------------*/
         else
         begin
                select   @sourcestring=substring(@text,1,@fieldlen)
                 -----------------------------------------------------------------------------------------------------
                 select   @splitstr=@sourcestring,
                  @splitlen=charindex(',',@splitstr),
                  @length=datalength(@splitstr)

          while @length>0
          begin
          --------------------------------------------------------------------------------------
           if      @splitlen=0
                         set @splitlen=@length+1

           set     @commastr
           insert @returntable (subcolstr) values(substring(@commastr,1,@splitlen-1))
           select @splitstr = substring(@commastr,@splitlen+1,@length),
                         @length=datalength(@splitstr),
                         @splitlen=charindex(',',@splitstr)
                 --------------------------------------------------------------------------------------
                 end
         end
         --------------------------------------------------------------------------------------------------------------
         end_label:
         return
end
GO

0 0