Oracle使用regexp_substr按字符分割如何不忽略空值？

使用regexp_substr对带分隔符的字符串做分割时，默认采用[^分隔符]+的匹配规则会自动跳过空字段，导致分割位置错位。以@为分隔符、测试字符串hello@@world为例，默认写法的返回结果和预期存在明显偏差：

-- 预期返回hello，实际返回hello（结果正常）
select regexp_substr('hello@@world', '[^@]+', 1, 1)
from dual;

-- 预期返回null（两个@中间的空字段），实际返回world（位置错位）
select regexp_substr('hello@@world', '[^@]+', 1, 2)
from dual;

-- 预期返回world，实际返回null（位置错位）
select regexp_substr('hello@@world', '[^@]+', 1, 3)
from dual;

曾尝试过仅支持|作为分隔符的现有方案，但无法适配任意分隔符的使用需求。以下是可落地的实现方式：

方案1：调整正则规则，直接通过regexp_substr实现 #

[^分隔符]+的逻辑要求匹配至少1个非分隔符字符，是导致空值被跳过的核心原因。将匹配规则调整为非贪婪匹配分隔符间隔内容、通过捕获组提取字段值，即可实现空值保留，且支持任意单字符分隔符：

select 
  regexp_substr('hello@@world', '(.*?)(@|$)', 1, 1, null, 1) as col1,
  regexp_substr('hello@@world', '(.*?)(@|$)', 1, 2, null, 1) as col2,
  regexp_substr('hello@@world', '(.*?)(@|$)', 1, 3, null, 1) as col3
from dual;

参数说明：

• 正则(.*?)(@|$)分为两个捕获组：第一组.*?非贪婪匹配0到多个任意字符（支持空内容），第二组@|$匹配分隔符或字符串结尾
• 函数最后一个参数1表示提取第一个捕获组的内容，也就是分隔符之间的实际字段值，空字段会正常返回null
• 更换分隔符时，只需将正则中的@替换为实际使用的分隔符即可；如果分隔符是.、*、+这类正则特殊元字符，需要在前面加转义符\，例如分隔符为.时正则写为(.*?)(\.|$)
• 如果待分割字符串包含换行符，可将函数第5个参数从null改为'n'，确保.可以匹配换行内容，避免截断异常

方案2：基于字符串位置计算的无正则替代方案 #

如果不想依赖正则匹配，或需要兼容多字符分隔符场景，可以通过instr计算分隔符位置、结合substr截取字段，完全避免空值跳过问题，兼容性更强：

-- 通用分割语句，修改str和delimiter取值即可适配任意场景
with params as (
  select 
    'hello@@world' as target_str,
    '@' as delimiter
  from dual
)
select 
  substr(
    target_str,
    case when level = 1 then 1 else instr(target_str, delimiter, 1, level-1) + length(delimiter) end,
    case when instr(target_str, delimiter, 1, level) = 0 
         then length(target_str) 
         else instr(target_str, delimiter, 1, level) - (case when level=1 then 0 else instr(target_str, delimiter,1,level-1) end) - length(delimiter) end
  ) as split_value
from params
connect by level <= length(target_str) - length(replace(target_str, delimiter)) + 1;

执行上述语句会按顺序返回hello、null、world三个值，完全符合预期。

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