REGEXP_LIKE instead of like

Question

Could You pls help me to type the condition using REGEXP_LIKE only one if it is doable :

WHERE ITEM_DESC LIKE '%P1%' AND ITEM_DESC NOT LIKE '%P2%'

Answer 1

Since Oracle does not support lookarounds, you need to get a little creative. Here's one way. Using a CTE, the first WITH just sets up test data. The second, ps1, contains only rows where the string contains 'PS1'. From those, select rows where the string does not contain 'PS2'. Only one REGEXP_LIKE like you asked for!

WITH tbl(ID, str) AS (
  SELECT 1, 'test PS1 string' FROM dual UNION ALL
  SELECT 2, 'test PS1 PS2 string' FROM dual UNION ALL
  SELECT 3, 'test PS2 string' FROM dual
),
ps1(ID, str) AS (
SELECT ID, str
FROM tbl
WHERE REGEXP_LIKE(str, '.*PS1.*')
)
SELECT ID, str
FROM ps1
WHERE NOT str LIKE '%PS2%';


        ID STR                
---------- -------------------
         1 test PS1 string    
1 row selected.