SQLSERVER-取出用分隔符分开字符串

if object_id(N'dbo.splitSTR') is not null drop FUNCTION dbo.splitSTR
go

create FUNCTION dbo.splitSTR(
@s varchar(8000),   --要分拆的字符串
@split varchar(10), --数据分隔符
@pos int            -- 取第几个
)RETURNS varchar(100)
AS
BEGIN
DECLARE @splitlen int, @re varchar(100)
SET @splitlen=LEN(@split+'a') - 2
WHILE CHARINDEX(@split,@s) > 0 AND @pos > 0
SELECT
@re = LEFT(@s,CHARINDEX(@split,@s)-1),
@s=STUFF(@s,1,CHARINDEX(@split, @s)+@splitlen,''),
@pos = @pos - 1
RETURN(CASE 
WHEN @pos = 0 THEN @re 
WHEN @pos = 1 THEN @s
ELSE NULL END)
END