字符分割（表值函数）

USE [自己的数据库名]
GO
/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 2020/12/9 13:08:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split]
(
    @sour NVARCHAR(MAX),
    @sep NVARCHAR(10)
)
RETURNS @array TABLE
(
    Item NVARCHAR(MAX)
)
AS
BEGIN
/*
--测试：select * from Split('1|2A|3BC|4DEF|5|', '|')
*/

    DECLARE @index INT,
            @item NVARCHAR(MAX),
            @temp NVARCHAR(MAX);
    SET @temp = @sour;
    SET @index = CHARINDEX(@sep, @temp);
    WHILE (@index <> 0)
    BEGIN
        SET @item = SUBSTRING(@temp, 1, @index - 1);
        INSERT INTO @array
        (
            Item
        )
        VALUES
        (@item);
        SET @temp = SUBSTRING(@temp, @index + 1, LEN(@temp) - @index);
        SET @index = CHARINDEX(@sep, @temp);
    END;
    SET @item = @temp;
    IF (LEN(@item) > 0)
        INSERT INTO @array
        (
            Item
        )
        VALUES
        (@item);
    RETURN;
END;