SQLServer截取参数split升级版

CREATE FUNCTION [dbo].[f_split_a]
(
@Expression varchar(8000) – 原参数串
, @Delimiter varchar(10) – 参数分隔符
, @index int – 要获取第几个参
)
RETURNS varchar(1000)
AS
BEGIN
declare @i int, @dl int, @l int, @value varchar(2000)
– @i获取字符出现的位置 @l 获取原参数串的长度
select @i = charindex(@Delimiter, @Expression), @l = len(@Expression)
– @dl 获取分隔符出现次数
select @dl=len(@Expression)-len(REPLACE(@Expression,@Delimiter,’’))
– 分隔符出现的次数大于等于要获取第N个参数
if @dl >= @index
begin
while (@index > 0 )
begin
select @value = ltrim(rtrim(left(@Expression, @i - 1))), @Expression = substring(@Expression, @i + 1, @l)
–insert into @Values (value) values(@value)
select @i = charindex(@Delimiter, @Expression), @l = len(@Expression)
set @index = @index - 1
end
end
return @value
END

eg:
select dbo.f_split_a(‘第一个参|第2个参|第3个参|第4个参|第5个参|第6个参|’,’|’,1)

---

第一个参