Oracle把逗号分隔的数据按几行返回|极客笔记

Oracle把逗号分隔的数据按几行返回

在实际工作中，我们经常会遇到需要对逗号分隔的数据进行处理的情况，特别是在处理一些文本数据或者CSV文件时。在Oracle数据库中，我们可以利用一些函数来将逗号分隔的数据拆分成多行数据，方便后续的处理。

使用REGEXP_SUBSTR函数拆分数据

Oracle数据库中提供了一个非常方便的函数REGEXP_SUBSTR，可以用来按照指定的正则表达式对字符串进行拆分。我们可以利用这个函数来把逗号分隔的数据按行返回。

下面是一个简单的示例，假设我们有一段逗号分隔的数据，我们想把它们按行返回：

SELECT REGEXP_SUBSTR('Apple,Orange,Banana,Grape', '[^,]+', 1, LEVEL) AS fruit
FROM dual
CONNECT BY REGEXP_SUBSTR('Apple,Orange,Banana,Grape', '[^,]+', 1, LEVEL) IS NOT NULL;

在这个示例中，我们首先使用了REGEXP_SUBSTR函数来将字符串'Apple,Orange,Banana,Grape'按照逗号拆分成单个的水果名称，并将其命名为fruit。然后利用CONNECT BY子句来不断重复这个过程，直到没有剩余的数据了。

运行以上SQL语句，我们将得到以下结果：

FRUIT
------
Apple
Orange
Banana
Grape

可以看到，逗号分隔的数据已经按行返回，分别是Apple、Orange、Banana和Grape。

使用XML函数拆分数据

除了REGEXP_SUBSTR函数外，我们还可以利用XML函数来拆分数据。Oracle数据库中提供了XMLTABLE这个函数，我们可以使用它来将逗号分隔的数据转换成XML格式，再通过XPath语法来获取每一行的数据。

下面是一个示例，同样是将逗号分隔的数据按行返回：

SELECT COLUMN_VALUE AS fruit
FROM (
    SELECT XMLTYPE('<fruits><fruit>' || REPLACE('Apple,Orange,Banana,Grape', ',', '</fruit><fruit>') || '</fruit></fruits>') AS xml_data
    FROM dual
)
CROSS JOIN XMLTABLE('/fruits/fruit' PASSING xml_data);

在这个示例中，我们首先利用REPLACE函数将原始的字符串'Apple,Orange,Banana,Grape'中的逗号替换成'</fruit><fruit>'，再用XMLTYPE函数将其转换成XML格式。然后利用XMLTABLE函数和XPath语法/fruits/fruit来获取每一个水果的名称。

运行以上SQL语句，我们同样可以得到与上文类似的结果：