sql split函数用法

SQL Server中没有内置的split函数，但可以使用自定义函数来实现字符串的拆分。以下是两种常见的方法：

方法一：使用临时表作为数组

CREATE FUNCTION dbo.SplitString(@string VARCHAR(MAX), @delimiter VARCHAR(2))
RETURNS @result TABLE (col VARCHAR(20))
AS
BEGIN
    WHILE (CHARINDEX(@delimiter, @string) <> 0)
    BEGIN
        INSERT INTO @result (col)
        VALUES (SUBSTRING(@string, 1, CHARINDEX(@delimiter, @string) - 1))
        
        SET @string = STUFF(@string, 1, CHARINDEX(@delimiter, @string), '')
    END
    
    INSERT INTO @result (col)
    VALUES (@string)
    
    RETURN
END

使用示例：

SELECT * FROM dbo.SplitString('dfkd,dfdkdf,dfdkf,dffjk', ',')

方法二：使用递归CTE（公共表表达式）

CREATE FUNCTION dbo.SplitString(@string VARCHAR(MAX), @delimiter VARCHAR(2))
RETURNS @result TABLE (col VARCHAR(20))
AS
BEGIN
    WITH cte AS (
        SELECT 
            LEFT(@string, CHARINDEX(@delimiter, @string) - 1) AS col,
            RIGHT(@string, LEN(@string) - CHARINDEX(@delimiter, @string)) AS remaining
        UNION ALL
        SELECT
            LEFT(remaining, CHARINDEX(@delimiter, remaining) - 1),
            RIGHT(remaining, LEN(remaining) - CHARINDEX(@delimiter, remaining))
        FROM cte
        WHERE CHARINDEX(@delimiter, remaining) > 0
    )
    INSERT INTO @result (col)
    SELECT col FROM cte
    
    RETURN
END

使用示例：

SELECT * FROM dbo.SplitString('dfkd,dfdkdf,dfdkf,dffjk', ',')