Oracle 在行中通过Oracle SQL查询拆分逗号分隔的列值

在本文中，我们将介绍如何使用Oracle SQL查询来拆分包含逗号分隔值的列。逗号分隔值的列在数据库中很常见，特别是当需要对存储多个值的列表进行查询和分析时。

方法一：使用SUBSTRING和INSTR函数

一个常见的方法是使用SUBSTRING和INSTR函数来拆分逗号分隔的值。SUBSTRING函数用于提取字符串的一部分，而INSTR函数用于定位特定字符在字符串中的位置。

下面是一个示例表employees，它包含了一个名为skills的包含逗号分隔技能的列：

employee_id	name	skills
1	John	SQL,Java,C#
2	Mary	Python,PHP
3	Michael	Java,HTML,CSS
4	Lisa	SQL,JavaScript

要按技能拆分行，我们可以执行以下查询：

SELECT employee_id, name, 
SUBSTR(skills, 1, INSTR(skills, ',', 1, 1) - 1) AS skill1,
SUBSTR(skills, INSTR(skills, ',', 1, 1) + 1, 
INSTR(skills, ',', 1, 2) - INSTR(skills, ',', 1, 1) - 1) AS skill2,
SUBSTR(skills, INSTR(skills, ',', 1, 2) + 1,
INSTR(skills, ',', 1, 3) - INSTR(skills, ',', 1, 2) - 1) AS skill3
FROM employees;

以上查询将返回以下结果：

employee_id	name	skill1	skill2	skill3
1	John	SQL	Java	C#
2	Mary	Python	PHP
3	Michael	Java	HTML	CSS
4	Lisa	SQL	JavaScript

这个查询使用了SUBSTR函数来提取每个技能的部分字符串。INSTR函数用于定位每个逗号的位置，并计算出所需字符串的起始点和结束点。

在这个示例中，我们只拆分了包含最多三个技能的列。如果需要拆分更多的技能，我们只需在查询中添加更多的SUBSTR函数，并相应地调整逗号的位置。

方法二：使用REGEXP_SUBSTR函数

Oracle还提供了一个更灵活的函数REGEXP_SUBSTR，它使用正则表达式来匹配和提取字符串的一部分。

下面是使用REGEXP_SUBSTR函数的示例查询：

SELECT employee_id, name,
REGEXP_SUBSTR(skills, '[^,]+', 1, 1) AS skill1,
REGEXP_SUBSTR(skills, '[^,]+', 1, 2) AS skill2,
REGEXP_SUBSTR(skills, '[^,]+', 1, 3) AS skill3
FROM employees;

以上查询将返回与第一个方法相同的结果。

REGEXP_SUBSTR函数接受四个参数：要匹配的字符串，正则表达式，开始匹配的位置和匹配的次数。正则表达式[^,]+表示匹配不包含逗号的连续字符。通过在查询中调整正则表达式和匹配次数，我们可以拆分任意数量的值。

方法三：使用CONNECT BY LEVEL和SUBSTRING函数

另一种方法是使用CONNECT BY LEVEL子句和SUBSTRING函数。CONNECT BY LEVEL子句在Oracle中用于生成具有连续数字的虚拟表。

以下是使用CONNECT BY LEVEL和SUBSTRING函数的示例查询：

SELECT employee_id, name,
SUBSTR(skills, INSTR(skills, ',', 1, LEVEL - 1) + 1, 
INSTR(skills, ',', 1, LEVEL) - INSTR(skills, ',', 1, LEVEL - 1) - 1) AS skill
FROM employees
CONNECT BY LEVEL <= LENGTH(skills) - LENGTH(REPLACE(skills, ',', '')) + 1;