Regex in Oracle - How can I check if a word does not occur?-CodePudding

I am currently using regex in Oracle to search a text for certain words. For example, if I want to search for the number of times "good performance" occurs, I want to exclude all sequences in which "not" appears. So I want to filter "good performance" and exclude "not good performance". On regex build pages it would work like this:

(?!not) (good performance).

But unfortunately in my script it doesn't work. What would be the corresponding expression in Oracle? Currently I used:

SELECT REGEXP_SUBSTR ('not good performance',
        '(?!not) (\sgood performance)',1,1,'i',2) 
FROM DUAL

CodePudding user response：

You can use:

SELECT value 
FROM   table_name
WHERE  REGEXP_SUBSTR (
         value,
         '(not\s )?(good performance)',
         1,
         1,
         'i',
         1
       ) IS NULL;

or, without regular expressions:

SELECT value 
FROM   table_name
WHERE  LOWER(value) LIKE '%good performance%'
AND    LOWER(value) NOT LIKE '%not good performance%';

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT  'Good Performance' FROM DUAL UNION ALL
SELECT  'No, it is not. Good performance!' FROM DUAL UNION ALL
SELECT  'NOT GOOD PERFORMANCE' FROM DUAL;

Both output:

VALUE
Good Performance
No, it is not. Good performance!

fiddle

CodePudding user response：

Do you really need regular expressions? How about instr?

SQL> with test (col) as
  2    (select 'that is not good performance at all' from dual union all
  3     select 'yes, it is good performance!'        from dual
  4    )
  5  select *
  6  from test
  7  where instr(col, 'good performance') > 0
  8    and instr(col, 'not good performance') = 0;

COL
-----------------------------------
yes, it is good performance!

SQL>