Oracle如何用REGEXP_SUBSTR提取分号分隔字符串符合规则的片段

需求规则 #

提取分号分隔字符串的内容，规则优先级如下：

• 优先返回任意一个由两个#分隔、具备完整三段结构的片段（格式为x#y#z，三段内容均非空）
• 若不存在符合上述结构的片段，返回字符串中的第一个分号分隔片段

输入输出对应示例 #

2#9#;2#37#65 ->  2#37#65
2#9#;2#37#65;2#37#  -> 2#37#65 
2#9#;2#37#65;2#37#;2#37#56  -> 2#37#65 或 2#37#56
2#37#65;2#99  -> 2#37#65
3#9#;3#37#65;3#37#36;2#37#56  -> 3#37#65 或 3#37#36 或 2#37#56
2#37#;2#99#  -> 2#37 或 2#99#（该场景返回任意值均可）

已尝试的无效写法 #

以下两种SQL写法均无法满足需求：

regexp_substr('2#9#;2#37#65;2#37#','#[^;]+',1)
SUBSTR(REGEXP_SUBSTR(SUBSTR(uo_filiere,1,INSTR(uo_filiere,';',1)-1), '#[^#]+$'),2)

正确实现（Oracle SQL） #

用正则优先匹配三段结构片段，无匹配时兜底取第一个分号分隔段即可，完整写法：

SELECT COALESCE(
  -- 取第一个符合x#y#z完整三段结构的分号分隔片段
  REGEXP_SUBSTR(uo_filiere, '[^;]+#[^;]+#[^;]+', 1, 1),
  -- 无匹配时兜底取第一个分号分隔片段
  REGEXP_SUBSTR(uo_filiere, '[^;]+', 1, 1)
) AS extract_result
FROM your_table_name;

逻辑说明 #

• 正则[^;]+#[^;]+#[^;]+的匹配逻辑：定位不含分号的连续字符+#+不含分号的连续字符+#+不含分号的连续字符，正好对应分号分隔下的完整三段x#y#z结构
• 若需要返回其他符合三段结构的片段（而非第一个），只需调整REGEXP_SUBSTR的第四个参数（匹配序号）即可，比如传2就返回第二个符合结构的片段
• 所有示例场景均验证通过：存在三段结构时优先返回对应值，无三段结构时返回第一个片段，符合需求规则

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