Oracle regexp_substr处理相同varchar返回结果不一致问题

在RFDGENERIC表中存储有varchar类型字段值 50% REC.PES/50% PTT，需求为按/字符分割该字符串。
首次直接查询原表使用的SQL如下：

select
    regexp_substr(DESCR, '[^/]+', 1, level) value
from 
    RFDGENERIC 
where 
    id = 14966150
    connect by level <= length(DESCR) - length(replace(DESCR, '/')) + 1
             and prior DESCR = DESCR
             and prior sys_guid() is not null;

该查询返回结果异常，共返回3行：

50% REC.PES
50% PTT
50% PTT

随后创建测试表TEST_SPLIT，将RFDGENERIC表中id=14966150的对应记录插入测试表，建表、插入与查询SQL如下：

CREATE TABLE TEST_SPLIT
(
    ID NUMBER(20),
    DESCR VARCHAR2(128 char)
);

INSERT INTO TEST_SPLIT (id, DESCR)
    SELECT id, DESCR 
    FROM RFDGENERIC 
    WHERE DESCR = '50% REC.PES/50% PTT' 
      AND id = 14966150;

SELECT
    regexp_substr(DESCR, '[^/]+', 1, level) value
FROM
    TEST_SPLIT 
WHERE
    id = 14966150
      connect by level <= length(DESCR) - length(replace(DESCR, '/')) + 1
             and prior DESCR = DESCR
             and prior sys_guid() is not null;

该查询返回结果正确，共返回2行：

50% REC.PES
50% PTT

已知前提 #

• 两张表对应字段的数据类型完全一致。
• 已尝试参考公开技术文档中介绍的其他字符串分割方法，直接查询原表仍出现相同的重复结果问题。
• 数据库环境信息：

Oracle (ver. Oracle Database 19c Enterprise Edition Release 
 19.0.0.0.0 - Production Version 19.15.0.0.0)

 Case sensitivity: plain=upper, delimited=exact
 Driver: Oracle JDBC driver (ver. 21.5.0.0.0, JDBC4.3)

两种查询场景的核心差异是什么？为何直接查询原表时会返回错误的重复结果？

两个场景的本质差异是直接查原表时，where id = 14966150过滤后的初始结果集不是1行，而测试表中对应id只有1行数据。
你用的connect by递归写法，只加了prior DESCR = DESCR和prior sys_guid() is not null的循环终止条件，没有绑定行级唯一标识，当初始结果集有多行时，递归会跨行走分支，直接产生重复结果。
常见的触发原因有两个：

• 原表RFDGENERIC中id=14966150实际存在2条记录，其中至少1条的DESCR字段值和你看到的50% REC.PES/50% PTT视觉上一致：要么带不可见特殊字符（比如末尾多了零宽空格、不可见的额外/），要么是完全重复的脏数据。
• 你插入测试表时额外加了DESCR = '50% REC.PES/50% PTT'的精确匹配条件，刚好把异常的脏记录过滤掉了，最终测试表里只存了1条符合预期的记录，所以递归结果正常。

快速验证方法 #

直接执行下面的SQL查询原表，即可快速定位根因：

select 
  id, 
  DESCR, 
  length(DESCR) as 字符串长度,
  length(DESCR) - length(replace(DESCR, '/')) as 斜杠数量
from RFDGENERIC 
where id = 14966150;

如果返回行数大于1，或者斜杠数量大于1，必然会出现你遇到的重复返回问题。

修正写法 #

不管原表匹配到多少行，只要在递归条件里绑定行唯一主键，强制递归只在当前行内部循环，就能稳定得到正确结果：

select
    regexp_substr(DESCR, '[^/]+', 1, level) value
from 
    RFDGENERIC 
where 
    id = 14966150
connect by level <= length(DESCR) - length(replace(DESCR, '/')) + 1
         and prior id = id -- 绑定唯一主键，禁止跨行递归产生重复
         and prior sys_guid() is not null;

12c及以上版本更推荐用lateral关联递归查询的写法，性能更好也不会出现循环、重复问题。

内容的提问来源于stack exchange，提问作者Norayr Gharibyan