自己写的一个sql server中经常需要用到的split用户函数

CREATE FUNCTION SYS_SPLIT(@srcStr NVARCHAR(MAX),@splitStr NVARCHAR(100))    
RETURNS  @tmp TABLE(        
    id        INT  IDENTITY PRIMARY KEY,      
    content   NVARCHAR(MAX)    
)    
AS   
BEGIN
    DECLARE @str    NVARCHAR(MAX);
    DECLARE @len    INT;
    DECLARE @num    INT;
    DECLARE @idx    INT;
    DECLARE @start  INT;
    SET @num   = LEN(@splitStr);
    SET @len   = LEN(@srcStr);
    SET @idx   = 0;
    SET @start = 0;
    WHILE @idx <= @len
    BEGIN
        IF LEFT(RIGHT(@srcStr, @len - @idx), @num) = @splitStr
        BEGIN
            SET @str = LEFT(RIGHT(@srcStr, @len - @start), @idx - @start);
            IF LEN(@str) > 0
            BEGIN
                INSERT INTO @tmp SELECT @str;
            END
            SET @idx   = @idx + @num;
            SET @start = @idx;
        END
        ELSE
        BEGIN
            SET @idx = @idx + 1;
        END
    END
	IF @start != @idx
	BEGIN
        SET @str = LEFT(RIGHT(@srcStr, @len - @start), @idx - @start - 1);
        IF LEN(@str) > 0
        BEGIN
            INSERT INTO @tmp SELECT @str;
        END
    END
    RETURN
END

另一种写法：

CREATE FUNCTION MY_SPLIT(@srcStr NVARCHAR(MAX),@splitStr NVARCHAR(100))    
RETURNS  @tmp TABLE(        
    id        INT  IDENTITY PRIMARY KEY,      
    content   NVARCHAR(MAX)    
)    
AS   
BEGIN
    DECLARE @str   NVARCHAR(MAX);
    DECLARE @len   INT;
    DECLARE @idx   INT;
    SET @len     = LEN(@splitStr);
    WHILE LEN(@srcStr) > 0
    BEGIN
        SET @idx = CHARINDEX(@splitStr, @srcStr);
        IF @idx  = 0
        BEGIN
            SET @str    = @srcStr;
            SET @srcStr = '';
        END
        ELSE
        BEGIN
            SET @str    = LEFT(@srcStr, @idx - 1);
            SET @srcStr = RIGHT(@srcStr, LEN(@srcStr) - LEN(@str) - @len);
        END
        IF LEN(@str) > 0
            INSERT INTO @tmp SELECT @str;
    END
    RETURN
END

需要注意的是，上面的方法是一个通用的解决思路，稍稍修改，就可以用到各sql数据库，

而微软从sql server2016开始，内置了STRING_SPLIT(sourceStr,  splitStr)函数，大家可以直接用，不用自己写split用户函数了