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 @[email protected];
end
while(CHARINDEX(@VarcharB,@VarcharA)>0 and @i<@times)
begin
set @[email protected]+ ( SUBSTRING(@VarcharA,1,CHARINDEX(@VarcharB,@VarcharA)-1))[email protected];
set @VarcharA=SUBSTRING(@VarcharA,(CHARINDEX(@VarcharB,@VarcharA)+1),LEN(@VarcharA));
set @[email protected]+1;
end

end
return @ret;
end

SQL 字串截取 類似split