SQL字符串Split

SQL字符串Split

代码：

GO
/****** Object:  UserDefinedFunction [dbo].[StringSplit]    Script Date: 2018/5/25 9:38:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[StringSplit]
(
    @Text NVARCHAR(MAX),
    @Sign NVARCHAR(MAX)
)
RETURNS
@ResultTable TABLE
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    TextValue NVARCHAR(1024)
)
AS
BEGIN
    DECLARE @StartIndex INT
    DECLARE @FindIndex  INT
    DECLARE @Content VARCHAR(4000)

    -- 和函数CHARINDEX有关CHARINDEX
    SET @StartIndex = 1
    SET @FindIndex=0


    WHILE(@StartIndex<=LEN(@Text))
    BEGIN
        SET @FindIndex=CHARINDEX(@Sign, @Text, @StartIndex)
        IF(@FindIndex=0 OR @FindIndex IS NULL)
        BEGIN
            -- 查找完毕
            SET @FindIndex=LEN(@Text)+1
        END

        SET @Content=LTRIM(RTRIM(SUBSTRING(@Text, @StartIndex, @FindIndex-@StartIndex)))

        -- 下次查找的位置
        SET @StartIndex=@FindIndex+1

        -- 插入结果 
        IF(LEN(@Content)>0)
        BEGIN
            INSERT INTO @ResultTable(TextValue) VALUES (@Content) 
        END
    END
    RETURN
END
GO

调用：

SELECT * FROM dbo.StringSplit('a,b,c,d,e,f,g', ',')

结果：