Oracle使用REGEXP_LIKE匹配多字符串条件无结果问题求解

Oracle数据库USER_DATA表中，KEYWORD列以下划线_为分隔符，拼接存储了同行其余所有列的字段值，示例表数据如下：

NAME  PHONE_NUMBER  COMPANY  ADDRESS  ZIPCODE    KEYWORD
ABCD  9849523459    MICRO    RAJAHMU  532819     ABCD_9849523459_MICRO_RAJAHMU_532819
ABCD  8628738646    INFOS    KAKINAD  532775     ABCD_8628738646_INFOS_KAKINAD_532775
ABCD  8473874381    ICUBE    RAVULAP  537238     ABCD_8473874381_ICUBE_RAVULAP_537238

执行以下SQL时返回0条结果，无法得到预期数据：

SELECT * FROM USER_DATA WHERE 1=1 AND REGEXP_LIKE ('KEYWORD', 'ABCD_MICRO_RAVULAP', 'i'));

预期返回2条记录：包含MICRO的第一行、包含RAVULAP的第三行，排除无匹配关键词的INFOS行。

原有SQL存在3个问题：

• 字段引用错误：'KEYWORD'加了单引号，数据库会将其识别为字符串常量，而非表中的KEYWORD列，永远无法匹配列值
• 语法错误：条件末尾多写了1个右括号，执行会直接触发语法报错
• 正则逻辑错误：正则中下划线_是匹配任意单个字符的元字符，直接写ABCD_MICRO_RAVULAP代表要求字符串连续匹配「ABCD + 任意单个字符 + MICRO + 任意单个字符 + RAVULAP」，示例数据中无符合该规则的记录。

根据预期返回结果，匹配逻辑为：KEYWORD中存在ABCD，且后续任意位置出现MICRO或RAVULAP即可，正确SQL如下：

SELECT * FROM USER_DATA 
WHERE 1=1 
AND REGEXP_LIKE(
  KEYWORD,
  'ABCD.*(MICRO|RAVULAP)',
  'i'
);

正则规则说明：

• ABCD：匹配字符串中的ABCD片段
• .*：匹配ABCD之后任意长度的任意字符，兼容中间夹的其他字段值
• (MICRO|RAVULAP)：匹配后续出现的MICRO或者RAVULAP片段
• 'i'：匹配时不区分大小写

如果实际需求为必须同时包含ABCD、MICRO、RAVULAP三个片段，不要求顺序和连续位置，可以使用正向预查写法：

SELECT * FROM USER_DATA 
WHERE 1=1 
AND REGEXP_LIKE(
  KEYWORD,
  '(?=.*ABCD)(?=.*MICRO)(?=.*RAVULAP)',
  'i'
);

注意：该写法在当前示例数据下不会返回结果，因为没有记录同时包含MICRO和RAVULAP两个片段。

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