在Oracle数据库的实际开发场景中，我们经常会遇到字段中存储着用固定分隔符拼接的字符串的情况，比如用户表中某个字段存储了用户拥有的多个角色ID，用逗号分隔，而业务需求是将这些ID拆分成单独的行来和角色表做关联查询。本文将介绍两种常用的Oracle字符串分割实现方法，满足不同的业务场景需求。
方法一：使用REGEXP_SUBSTR配合CONNECT BY实现分割
这种方式是Oracle中最常用的字符串分割方案，不需要创建自定义函数，直接通过内置函数和层级查询就能完成需求。核心思路是通过REGEXP_SUBSTR函数按正则规则提取每一段分隔后的内容，再通过CONNECT BY生成连续的行号，直到没有更多可提取的内容为止。
实现步骤
- 确定原字符串的分隔符，比如逗号、竖线等
- 使用
REGEXP_SUBSTR函数按分隔符匹配每一段内容，正则中可以用[^分隔符]+匹配非分隔符的连续字符 - 通过
CONNECT BY LEVEL生成递增的行号，作为提取每一段的索引 - 设置终止条件，当
REGEXP_SUBSTR返回空值时停止生成行
代码示例
以下示例将原字符串苹果,香蕉,橘子,葡萄按逗号分割成多行：
-- 原字符串为逗号分隔的水果名称，分割为多行
SELECT
REGEXP_SUBSTR('苹果,香蕉,橘子,葡萄', '[^,]+', 1, LEVEL) AS split_result
FROM
DUAL
CONNECT BY
-- 当提取不到更多内容时停止循环
REGEXP_SUBSTR('苹果,香蕉,橘子,葡萄', '[^,]+', 1, LEVEL) IS NOT NULL;执行上述SQL后，会返回4行数据，分别是苹果、香蕉、橘子、葡萄。如果需要分割表中某个字段的内容，只需要把原字符串替换成对应的字段名即可，示例如下：
-- 分割表中某个字段的字符串内容
SELECT
t.id,
REGEXP_SUBSTR(t.fruit_list, '[^,]+', 1, LEVEL) AS single_fruit
FROM
fruit_table t
CONNECT BY
-- 避免同一行数据重复生成，通过PRIOR和主键限制
PRIOR t.id = t.id
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
AND REGEXP_SUBSTR(t.fruit_list, '[^,]+', 1, LEVEL) IS NOT NULL;方法二：创建自定义字符串分割函数
如果项目中频繁需要字符串分割功能，或者分割逻辑比较复杂，也可以创建自定义函数来实现。自定义函数的优势是可以封装逻辑，调用时更简洁，也方便统一修改分割规则。
函数创建示例
以下函数接收一个字符串和分隔符，返回分割后的结果集：
-- 创建自定义字符串分割函数
CREATE OR REPLACE FUNCTION split_string(
p_str IN VARCHAR2, -- 原字符串
p_delimiter IN VARCHAR2 -- 分隔符
) RETURN SYS.ODCIVARCHAR2LIST IS
v_result SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
v_start_idx NUMBER := 1;
v_end_idx NUMBER;
BEGIN
IF p_str IS NULL THEN
RETURN v_result;
END IF;
LOOP
-- 查找下一个分隔符的位置
v_end_idx := INSTR(p_str, p_delimiter, v_start_idx);
IF v_end_idx = 0 THEN
-- 没有更多分隔符，提取剩余内容
v_result.EXTEND;
v_result(v_result.COUNT) := SUBSTR(p_str, v_start_idx);
EXIT;
ELSE
-- 提取当前分隔符前的内容
v_result.EXTEND;
v_result(v_result.COUNT) := SUBSTR(p_str, v_start_idx, v_end_idx - v_start_idx);
v_start_idx := v_end_idx + LENGTH(p_delimiter);
END IF;
END LOOP;
RETURN v_result;
END split_string;
/函数调用示例
创建函数后，可以通过TABLE函数调用，示例代码如下：
-- 调用自定义分割函数
SELECT
COLUMN_VALUE AS split_result
FROM
TABLE(split_string('苹果,香蕉,橘子,葡萄', ','));两种方法的对比
我们可以通过下面的表格对比两种方法的适用场景：
| 对比项 | REGEXP_SUBSTR+CONNECT BY | 自定义函数 |
|---|---|---|
| 是否需要创建对象 | 不需要 | 需要创建函数 |
| 调用复杂度 | SQL语句较长，逻辑较复杂 | 调用简单，函数名清晰 |
| 性能表现 | 简单场景性能较好 | 复杂逻辑或高频调用时更优 |
| 适用场景 | 临时查询、简单分割需求 | 频繁使用、复杂分割规则 |
注意事项
在使用REGEXP_SUBSTR+CONNECT BY的方式时，要注意避免重复生成数据的问题。如果原表有多行数据，需要在CONNECT BY条件中添加PRIOR 主键 = 主键以及PRIOR DBMS_RANDOM.VALUE IS NOT NULL的条件，避免不同行的数据交叉生成重复行。
另外，如果分隔符是正则表达式中的特殊字符，比如点号、竖线等，需要在正则中对分隔符进行转义，比如分隔符是点号时，正则应该写成[^\\.]+，才能正确匹配非点号的字符。
字符串分割后如果需要和其他表做关联查询，建议在分割结果上添加原表的主键字段，方便后续做关联条件匹配，避免数据错乱。
Oracle字符串分割REGEXP_SUBSTRCONNECT BY多行转换修改时间：2026-06-06 23:23:33