Oracle SQL中如何按分号拆分变量值并输出为多列查询结果

该需求可以完全实现，Oracle没有提供直接将分隔字符串拆分至多列的单个专用函数，但可以通过内置字符串函数组合完成目标，根据拆分场景的不同可选择不同实现方式。

场景1：已知拆分后固定列数（示例为4列） #

如果提前明确分隔后的值个数固定，优先选择基础字符串函数实现，性能优于正则方案：

• 核心用到两个内置函数：INSTR定位分号的位置，SUBSTR根据位置截取对应段的内容

WITH temp_var AS (
  -- 此处定义待拆分的变量
  SELECT 'test;test1;test2;test3' AS split_val FROM dual
)
SELECT
  -- 截取第1段：第一个分号之前的内容
  SUBSTR(split_val, 1, INSTR(split_val, ';', 1, 1) - 1) col1,
  -- 截取第2段：第一个分号和第二个分号之间的内容
  SUBSTR(split_val, INSTR(split_val, ';', 1, 1) + 1, INSTR(split_val, ';', 1, 2) - INSTR(split_val, ';', 1, 1) - 1) col2,
  -- 截取第3段：第二个分号和第三个分号之间的内容
  SUBSTR(split_val, INSTR(split_val, ';', 1, 2) + 1, INSTR(split_val, ';', 1, 3) - INSTR(split_val, ';', 1, 2) - 1) col3,
  -- 截取第4段：最后一个分号之后的内容
  SUBSTR(split_val, INSTR(split_val, ';', 1, 3) + 1) col4
FROM temp_var;

执行上述语句后会返回4列，值依次为test、test1、test2、test3，完全匹配预期结果。

场景2：希望写法更简洁，不手动计算分号偏移量 #

可以使用正则函数REGEXP_SUBSTR实现，代码可读性更高：

WITH temp_var AS (
  SELECT 'test;test1;test2;test3' AS split_val FROM dual
)
SELECT
  REGEXP_SUBSTR(split_val, '[^;]+', 1, 1) col1,
  REGEXP_SUBSTR(split_val, '[^;]+', 1, 2) col2,
  REGEXP_SUBSTR(split_val, '[^;]+', 1, 3) col3,
  REGEXP_SUBSTR(split_val, '[^;]+', 1, 4) col4
FROM temp_var;

参数说明：

• [^;]+为正则匹配规则，代表匹配连续的非分号字符
• 第三个参数代表从字符串起始第1位开始匹配
• 第四个参数代表返回第N个匹配到的片段，对应结果的第N列

特殊情况适配 #

如果待拆分字符串中存在连续分号（即分隔后存在空值），上述正则写法会自动跳过空值，若需要保留空值，将正则规则调整为捕获组写法即可：

-- 以第一列为示例，其余列仅需修改第四个参数的序号即可
REGEXP_SUBSTR(split_val, '(.*?)(;|$)', 1, 1, NULL, 1) col1

场景3：拆分后列数动态不固定 #

如果无法提前确定拆分后的列数，无法直接写死静态SELECT语句，需要先统计字符串中分号的数量计算总列数，再通过动态SQL拼接对应数量的截取字段，最终执行拼接完成的语句得到结果。

内容的提问来源于stack exchange，提问作者Mahmoud Abdulkarim