Spur Spur - 17 days ago 5
SQL Question

regular expression to exclude if preceded by not or no

How do i write regular expression to find word bad which is not preceded by words not or no.

In the example below I should find Line 1 and Line 4.

Line 1: a bad rider

Line 2 : apple is not bad

Line 3 : there is no bad remarks.

Line 4 : no there is nothing bad


Is it possible to do it without lookahead as it is not supported in Oracle sql.

Answer

Try:

select *
from table
where regexp_like( column, 'bad' ) and NOT regexp_like( column, '(not|no) bad' )

or just (probably the fastest):

select *
from table
where  column like( '%bad%' ) 
  and not (column like '%not bad%' or column like '%no bad%');