基于逗号将一列拆分为多列(Oracle)

SELECT
    REGEXP_SUBSTR(my_column, '[^,]+', 1, 1) AS col1,
    REGEXP_SUBSTR(my_column, '[^,]+', 1, 2) AS col2,
    REGEXP_SUBSTR(my_column, '[^,]+', 1, 3) AS col3
FROM
    my_table;

WITH split_data AS (
    SELECT
        my_column,
        LEVEL AS rn,
        REGEXP_SUBSTR(my_column, '[^,]+', 1, LEVEL) AS part
    FROM
        my_table
    CONNECT BY
        REGEXP_SUBSTR(my_column, '[^,]+', 1, LEVEL) IS NOT NULL
        AND PRIOR my_column = my_column
        AND PRIOR SYS_GUID() IS NOT NULL
)
SELECT
    MAX(CASE WHEN rn = 1 THEN part END) AS col1,
    MAX(CASE WHEN rn = 2 THEN part END) AS col2,
    MAX(CASE WHEN rn = 3 THEN part END) AS col3
FROM
    split_data;

CREATE OR REPLACE FUNCTION split_string (
    p_string IN VARCHAR2,
    p_delimiter IN VARCHAR2 := ',',
    p_max_parts IN NUMBER := 100
) RETURN SYS.ODCIVARCHAR2LIST PIPELINED IS
    l_string VARCHAR2(32767) := p_string || p_delimiter;
    l_delimiter_length NUMBER := LENGTH(p_delimiter);
    l_pos NUMBER := 1;
BEGIN
    FOR i IN 1..p_max_parts LOOP
        l_pos := INSTR(l_string, p_delimiter, l_pos);
        EXIT WHEN l_pos = 0;
        PIPE ROW(SUBSTR(l_string, 1, l_pos - 1));
        l_string := SUBSTR(l_string, l_pos + l_delimiter_length);
        l_pos := l_pos + l_delimiter_length;
    END LOOP;
    RETURN;
END split_string;
/

SELECT
    COLUMN_VALUE AS col1,
    LEAD(COLUMN_VALUE, 1) OVER (ORDER BY ROWNUM) AS col2,
    LEAD(COLUMN_VALUE, 2) OVER (ORDER BY ROWNUM) AS col3
FROM
    TABLE(split_string(my_column))
WHERE
    ROWNUM <= 3;