工作遇到要截取字符串从某关键字开始结束符为有相同多个的某个
比如字符串:前面其他的;说明:ZEN,30V,5%,0.2,SE,M,T
create FUNCTION [dbo].[fn_Get_CmemoCut]
(
@cmemo VARCHAR(max),–原字符串
@key VARCHAR(50), –开始关键字
@delim VARCHAR(20), –结束符
@count int –在第几个结束符截
)
RETURNS VARCHAR(max)
AS
BEGIN
declare @p int=0 –位置
declare @n int=0 –计数
declare @f BIT=1
declare @returnstr varchar(max)=”
if charindex(@key,@cmemo)=0 return @returnstr
if @count>0
begin
if charindex(@key,@cmemo)>0
begin
set @cmemo=substring(@cmemo,charindex(@key,@cmemo),len(@cmemo))
end
end
while @f=1
begin
if charindex(@delim,@cmemo,@p+1)>1
begin
if @n=@count
begin
set @returnstr=substring(@cmemo,1,@p)
end
set @n=@n+1
set @p=charindex(@delim,@cmemo,@p+1)
end
else
begin
set @f=0
end
end
if @returnstr<>''
begin
set @returnstr=REPLACE(left(@returnstr,LEN(@returnstr)-1),@key,'')
end
return @returnstr
END
运行结果如图:
