PCB MS SQL 将字符串分割,并指定索引返回字符串(标量函数)

 

Create  FUNCTION [dbo].[SplitIndex]
(
    @str AS VARCHAR(max),
    @Index AS INT,
    @SplitChar AS VARCHAR(10) = '/'
)
RETURNS VARCHAR(50)
AS
BEGIN
     DECLARE @ReturnStr   varchar(50)   --待分拆的字符串
     DECLARE @tab TABLE(id int IDENTITY(1,1), col varchar(max)) --建立临时表保存分割后的字符                                                   
     DECLARE @splitlen INT
     SET @splitlen = LEN(@SplitChar+'a')-2
     WHILE CHARINDEX(@SplitChar,@str)>0
     BEGIN
        INSERT @tab VALUES(LEFT(@str,CHARINDEX(@SplitChar,@str)-1))
        SET @str = STUFF(@str,1,CHARINDEX(@SplitChar,@str)+@splitlen,'')
     END
     INSERT @tab VALUES(@str)
     SELECT @ReturnStr = col FROM @tab WHERE id = @Index
     IF (@ReturnStr IS NULL)
     BEGIN
         SET @ReturnStr = ''
     END
     RETURN @ReturnStr
END

 

示例代码：

SELECT [dbo].[SplitIndex]('1.5oz/1.0oz',1,'/')
SELECT [dbo].[SplitIndex]('1.5oz/1.0oz',2,'/')