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)
if @fieldlen >7000
begin
while @nbegin+1<[email protected]
begin
--------------------------------------------------------------------------------------------------
if @[email protected]>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 [email protected]
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
else
begin
select @sourcestring=substring(@text,1,@fieldlen)
-----------------------------------------------------------------------------------------------------
select @[email protected],
@splitlen=charindex(',',@splitstr),
@length=datalength(@splitstr)
while @length>0
begin
--------------------------------------------------------------------------------------
if @splitlen=0
set @[email protected]+1
set @commastr [email protected]
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
![Oracle的基本操作[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)