/*
获取字符串内的分隔段
*/
CREATE FUNCTION [dbo].[GETSPLITCHAR](@STR NVARCHAR(4000),@SPLITCHAR NVARCHAR(1),@SPLITLENGTH INT)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @RETURNSTR NVARCHAR(50)
DECLARE @i INT
DECLARE @COUNT INT
SET @COUNT = 1
SET @STR=RTRIM(LTRIM(UPPER(@STR+@SPLITCHAR)))
SET @i=CHARINDEX(@SPLITCHAR,@STR)
WHILE @i>=1
BEGIN
SET @RETURNSTR = SUBSTRING(@STR,1,@i-1)
SET @STR=SUBSTRING(@STR,@i+1,LEN(@STR)-@i)
SET @i=CHARINDEX(@SPLITCHAR,@STR)
IF @SPLITLENGTH = @COUNT
RETURN @RETURNSTR
SET @COUNT = @COUNT + 1
END
RETURN ''
END
调用:
SELECT dbo.GETSPLITCHAR('1|2|3|4|5' ,'|',5)