Oracle中REGEXP_REPLACE处理字符串分隔符失效及REGEXP_SUBSTR拆分数据异常问题求助

我看到你有一个用__##__作为分隔符的字符串，想要实现两个需求：一是截取从开头到第N个分隔符的内容，二是把整个字符串拆分成每个元素单独一行，但用REGEXP_REPLACE和REGEXP_SUBSTR时都遇到了问题，下面帮你逐个解决：

一、REGEXP_REPLACE参数顺序错误导致结果不符合预期 #

你当前写的REGEXP_REPLACE语句参数顺序搞反了！Oracle的REGEXP_REPLACE语法是：

REGEXP_REPLACE(source_str, pattern, replace_str, start_pos, occurrence)

你把「替换字符串」和「匹配次数」的位置弄混了——第三个参数是要替换成的内容，第四个才是指定替换第几次匹配的结果。另外你用的__##__.+是贪婪匹配，整个字符串里只会匹配一次（从第一个__##__到结尾），所以指定第2次匹配时自然找不到目标，原字符串也就不会变化。

如果你想保留前N个元素的拼接内容（比如前2个：Data 1__##__Data Two），可以用这个写法：

SELECT REGEXP_REPLACE(
    'Data 1__##__Data Two__##__Third Data__##__4th Data__##__',
    '^((.*?__##__){2})(.*)',  -- 分组匹配前2个元素+剩余内容
    '\1'                      -- 只保留前2个元素的部分
) FROM dual;

把正则里的{2}改成你需要的N值，就能灵活获取前N个元素的拼接结果。

如果只是想获取单个元素（比如第2个元素Data Two），可以直接用REGEXP_SUBSTR：

SELECT REGEXP_SUBSTR(
    'Data 1__##__Data Two__##__Third Data__##__4th Data__##__',
    '(.*?)(__##__|$)',
    1,
    2,
    NULL,
    1
) FROM dual;

二、REGEXP_SUBSTR拆分字符串为空的问题 #

你用的正则(?!__##__)是负向预查，它匹配的是「不在__##__前面的位置」，而不是分隔后的元素内容，所以自然返回空值。

要把分隔后的每个元素拆分成单独的行，正确的REGEXP_SUBSTR写法应该是这样：

SELECT REGEXP_SUBSTR(
    'Data 1__##__Data Two__##__Third Data__##__4th Data__##__',
    '(.*?)(__##__|$)',  -- 非贪婪匹配，直到分隔符或字符串结尾
    1,
    LEVEL,
    NULL,
    1                   -- 取第一个分组，即排除分隔符的元素内容
) AS split_data
FROM dual
CONNECT BY REGEXP_SUBSTR(
    'Data 1__##__Data Two__##__Third Data__##__4th Data__##__',
    '(.*?)(__##__|$)',
    1,
    LEVEL
) IS NOT NULL
AND PRIOR SYS_GUID() IS NOT NULL;  -- 避免层次查询产生重复行

执行后会得到你想要的结果：

Data 1
Data Two
Third Data
4th Data

备注：内容来源于stack exchange，提问作者TenG