Oracle按字符串过滤数据：仅含'Hello there'的记录查询

问题背景 #

Oracle数据库的tab1表存在如下数据：

Col1   Col2  Message (Value can be concatenation of multi messages string after "Hello there" can vary)
-----------------------------------------------------------
I1     L1    Message1|Message2|Hello there D1,D2,D3
I2     L2    Message1|MessageN|Hello there D3,D4,D1
I3     L3    Hello there D3,D5
I4     L4    Message3|Message4
I5     L5    Hello there DN,DN+1

需求：仅筛选出Message字段包含'Hello there'且不含Message1、Message2…MessageN等其他消息的记录（即第3、5条记录）。

当前尝试的SQL语句未得到预期结果，寻求高效实现方法：

select * from tab1 t1 where message like '%Hello there%' 
and not exists (select 1 from tab1 t2 where t1.col1=t2.col1 and t1.col2 =t2.col2 and ???)

解决方案 #

方法1：简单字符串匹配（性能最优） #

不符合要求的记录都带有|分隔的其他Message项，直接通过排除|来筛选：

SELECT * 
FROM tab1 
WHERE message LIKE '%Hello there%' 
  AND message NOT LIKE '%|%';

方法2：正则表达式匹配（适配复杂场景） #

如果存在特殊格式（比如Message含|但不是其他Message项），用正则精准排除所有MessageX格式的子串：

SELECT * 
FROM tab1 
WHERE REGEXP_LIKE(message, 'Hello there') 
  AND NOT REGEXP_LIKE(message, 'Message[0-9N]+');

方法3：优化你原有的NOT EXISTS写法 #

如果坚持用子查询思路，可拆分Message的分隔项，验证不存在以Message开头的子串：

SELECT * 
FROM tab1 t1 
WHERE t1.message LIKE '%Hello there%'
  AND NOT EXISTS (
    SELECT 1 
    FROM (
      SELECT REGEXP_SUBSTR(t1.message, '[^|]+', 1, LEVEL) AS msg_part
      FROM dual
      CONNECT BY REGEXP_SUBSTR(t1.message, '[^|]+', 1, LEVEL) IS NOT NULL
    ) sub
    WHERE sub.msg_part LIKE 'Message%'
  );

注：此方法适合需逐个验证子项的场景，但性能略低于前两种，数据量大时优先选前两种。

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