1、 取第几个分隔符前的字符串
create function [dbo].[split_str]
(@str nvarchar(4000), --需要拆分的字符串
@code varchar(10), --分隔符号
@no int ) --第n个,从0开始
returns varchar(200) --返回第n个值
as
begin
declare @intlen int
declare @count int
declare @indexb int
declare @indexe int
set @intlen=len(@code)
set @count=0
set @indexb=1
if @no=0
if charindex(@code,@str,@indexb)<>0
return left(@str,charindex(@code,@str,@indexb)-1)
else --如果分隔符不存在,则返回整个字符串
return @str
while charindex(@code,@str,@indexb)<>0
begin
set @count=@count+1
if @count=@no
break
set @indexb=@intlen+charindex(@code,@str,@indexb)
end
if @count=@no
begin
set @indexe=@intlen+charindex(@code,@str,@indexb)
if charindex(@code,@str,@indexe)<>0
return substring(@str,charindex(@code,@str,@indexb)+len(@code),charindex(@code,@str,@indexe)-charindex(@code,@str,@indexb)-len(@code))
else
return right(@str,len(@str)-charindex(@code,@str,@indexb)-len(@code)+1)
end
return ''
end
示例:
2、返回拆分后的table
CREATE FUNCTION [dbo].[f_splitSTR](
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
示例: