Oracle-将Oracle表中的多个逗号分隔值拆分为多行

在本文中，我们将介绍如何在Oracle表中将包含多个逗号分隔值的数据拆分为多行。这种情况通常出现在数据库中包含多个项目、标签或关键字的情况下。

方法1: 使用CONNECT BY子句和正则表达式

一种常见的方法是使用CONNECT BY子句和正则表达式将逗号分隔值拆分为多行。下面是一个示例，展示了如何在名为”mytable”的表中执行此操作：

SELECT regexp_substr(column_name, '[^,]+', 1, level) AS value
FROM mytable
CONNECT BY regexp_substr(column_name, '[^,]+', 1, level) IS NOT NULL
AND PRIOR column_name = column_name
AND PRIOR sys_guid() IS NOT NULL;

这个查询使用regexp_substr函数从逗号分隔的列中提取非逗号字符。CONNECT BY子句用于创建多行结果集，其中每一行都包含了从逗号分隔值中提取的一个项目。

下面是一个示例，展示了如何在名为”mytable”的表中使用这个查询：

CREATE TABLE mytable (column_name VARCHAR2(100));
INSERT INTO mytable VALUES ('A,B,C');
INSERT INTO mytable VALUES ('X,Y,Z');

SELECT regexp_substr(column_name, '[^,]+', 1, level) AS value
FROM mytable
CONNECT BY regexp_substr(column_name, '[^,]+', 1, level) IS NOT NULL
AND PRIOR column_name = column_name
AND PRIOR sys_guid() IS NOT NULL;

查询的结果如下：

VALUE
-----
A
B
C
X
Y
Z

方法2: 使用PL/SQL函数

除了使用SQL查询外，还可以使用PL/SQL函数来执行逗号分隔值到多行的转换。下面是一个示例函数，将逗号分隔值作为输入，返回一个表类型的结果：

CREATE OR REPLACE TYPE split_tbl_type IS TABLE OF VARCHAR2(100);
/

CREATE OR REPLACE FUNCTION split_string(
    p_string IN VARCHAR2
) RETURN split_tbl_type PIPELINED
AS
    l_start PLS_INTEGER := 1;
    l_end PLS_INTEGER;
BEGIN
    LOOP
        l_end := instr(p_string, ',', l_start);
        IF l_end > 0 THEN
            PIPE ROW (substr(p_string, l_start, l_end - l_start));
            l_start := l_end + 1;
        ELSE
            PIPE ROW (substr(p_string, l_start));
            EXIT;
        END IF;
    END LOOP;
    RETURN;
END;
/

SELECT * FROM TABLE(split_string('A,B,C'));

以上函数将逗号分隔值作为输入参数，并使用instr函数找到逗号在字符串中的位置，从而将其拆分为多行结果集。