WITH CTE_DATA AS (
    SELECT 'tom' AS NAME, 'apple,banana,cherry' AS FRUIT FROM DUAL
)
SELECT NAME, REGEXP_SUBSTR(FRUIT, '[^,]+', 1, LEVEL) AS FRUIT
FROM CTE_DATA
CONNECT BY REGEXP_SUBSTR(FRUIT, '[^,]+', 1, LEVEL) IS NOT NULL
;

CREATE TABLE IF NOT EXISTS numbers (n INT PRIMARY KEY);
INSERT INTO numbers
SELECT a.N + b.N * 10 + c.N * 100 + 1 AS n
FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c;
-- 使用数字表分割
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX('a,b,c,d', ',', numbers.n),
',',
-1
) AS part
FROM numbers
WHERE numbers.n <= LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')) + 1;

CREATE FUNCTION dbo.SplitString
(
    @String NVARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @Results TABLE 
(
    ID INT IDENTITY(1,1),
    Value NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @pos INT = 0
    DECLARE @slice NVARCHAR(MAX)

    IF RIGHT(@String, 1) != @Delimiter
        SET @String = @String + @Delimiter

    WHILE CHARINDEX(@Delimiter, @String) > 0
    BEGIN
        SET @pos = CHARINDEX(@Delimiter, @String)
        SET @slice = LEFT(@String, @pos - 1)
        
        INSERT INTO @Results (Value) VALUES (@slice)
        SET @String = STUFF(@String, 1, @pos, '')
    END
    
    RETURN
END

-- 使用示例
SELECT * FROM dbo.SplitString('张三,李四,王五', ',')

DECLARE @str NVARCHAR(100) = '苹果|香蕉|橙子';
DECLARE @separator CHAR(1) = '|';

SELECT 
    LTRIM(RTRIM(m.n.value('.[1]', 'NVARCHAR(100)'))) AS SplitValue
FROM 
    (SELECT CAST('<x>' + REPLACE(@str, @separator, '</x><x>') + '</x>' AS XML)) AS t(x)
CROSS APPLY 
    x.nodes('/x') AS m(n);