SQL 字符串截取 类似split

/*
表值函数
将一个字符串拆分成多个。
类似Split
*/

drop function F_SYS_Split
create function F_SYS_Split(@VarcharA varchar(1000),@VarcharB varchar(1))
returns @tab table(v varchar(100))
as
begin
if LEN(@VarcharA)>0 and LEN(@VarcharB)>0
begin
while(CHARINDEX(@VarcharB,@VarcharA)>0)
begin
insert into @tab(v)values ( SUBSTRING(@VarcharA,1,CHARINDEX(@VarcharB,@VarcharA)-1));
set @VarcharA=SUBSTRING(@VarcharA,(CHARINDEX(@VarcharB,@VarcharA)+1),LEN(@VarcharA));
end
insert into @tab(v)values (@VarcharA);
end
return;
end

select * from dbo.F_SYS_Split(‘1,4,2,4,‘,‘,‘);

/*
@VarcharA 要处理的字符串
@VarcharB 分割符
@times 次数

例:
select dbo.F_SYS_Split(‘A;B;C;E;A;D;F‘,‘;‘,4)-- 结果 A;B;C;E;
select dbo.F_SYS_Split(‘A,B,C,E,A;D;F‘,‘;‘,4)-- 结果 A,B,C,E,A;D;FA,B,C,E,A;D; 分割符小于4个原样输出

select dbo.F_SYS_Split(‘A,B,C,E,A;D;F‘,‘,‘,4)-- 结果 A,B,C,E, 分割符小于4个原样输出
select dbo.F_SYS_Split(‘A,B,C,E,A,D,F‘,‘,‘,6)--结果 A,B,C,E,A,D,

*/
create function F_SYS_Split(@VarcharA varchar(1000),@VarcharB varchar(1),@times int)
returns varchar(1000)
as
begin
declare @ret varchar(1000)=‘‘;
declare @i int=0;
if @times is null or @times<=0 set @times=4;

if LEN(@VarcharA)>0 and LEN(@VarcharB)>0
begin
if LEN(@VarcharA)-LEN(replace(@VarcharA,@VarcharB,‘‘))<3
begin
set @ret=@VarcharA;
end
while(CHARINDEX(@VarcharB,@VarcharA)>0 and @i<@times)
begin
set @ret=@ret+ ( SUBSTRING(@VarcharA,1,CHARINDEX(@VarcharB,@VarcharA)-1))+@VarcharB;
set @VarcharA=SUBSTRING(@VarcharA,(CHARINDEX(@VarcharB,@VarcharA)+1),LEN(@VarcharA));
set @i=@i+1;
end

end
return @ret;
end

SQL 字符串截取 类似split

标签：