sql 字符串按某分隔符拆分函数

CREATE FUNCTION splitStr
(
@String NVARCHAR(4000),
@Separator NVARCHAR(10)
)
RETURNS @strTable TABLE (strVal NVarchar(4000))
BEGIN
DECLARE @occu INT,
@tempVal NVARCHAR(4000),
@Counter INT
SET @Counter=0
IF SUBSTRING(@String,LEN(@String),1)<>@Separator
SET @String=@String+@Separator
SET @occu=(DATALENGTH(REPLACE(@String,@Separator,@Separator+'#'))-DATALENGTH(@String))/DATALENGTH(@Separator)
SET @tempVal=@String
WHILE @Counter<=@occu
BEGIN
SET @Counter=@Counter+1
IF LEN(SUBSTRING(@tempVal,1,CHARINDEX(@Separator,@tempVal)-1))>0
INSERT @strTable
       ( strVal )
VALUES  (SUBSTRING(@tempVal,1,CHARINDEX(@Separator,@tempVal)-1))
SET @tempVal=SUBSTRING(@tempVal,CHARINDEX(@Separator,@tempVal)+1,4000)
IF LEN(@tempVal)=0
BREAK
END
RETURN

END