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