regular expression to exclude if preceded by not or no

Question

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.

Use [`(?<!not )(?<!no )\bbad\b`](https://regex101.com/r/ywMEV6/1) — Wiktor Stribiżew, Nov 22 '16 at 19:30
I need to do it in Oracle regular express. Negative lookahead seems to be not supported. Is it possible to do it without negative lookahead? — snowrock, Nov 22 '16 at 20:14

score 0 · Answer 1 · answered Nov 22 '16 at 21:47

I believe this may get you a little closer and allow for when "not" or "bad" is part of another word. The regex looks for a space followed by the string "not" or "no" followed by a space then "bad" followed by a space or the end of the line (ensuring they are not part of another word). Add more test phrases until you are sure! i.e. Do you need to allow for special characters like if line 5 ended in a question mark?

SQL> with tbl(data) as (
  2    select 'Line 1: a bad rider' from dual union
  3    select 'Line 2 : apple is not bad' from dual union
  4    select 'Line 3 : there is no bad remarks.' from dual union
  5    select 'Line 4 : no there is nothing bad' from dual union
  6    select 'Line 5 : was that knot bad' from dual union
  7    select 'Line 6 : let''s play badminton' from dual union
  8    select 'Line 7 : let''s play no badminton' from dual
  9  )
 10  select *
 11  from tbl
 12  where not regexp_like(data, ' (not|no) bad( |$)');

DATA
---------------------------------
Line 1: a bad rider
Line 4 : no there is nothing bad
Line 5 : was that knot bad
Line 6 : let's play badminton
Line 7 : let's play no badminton

SQL>

krokodilko · Accepted Answer · 2016-11-22T20:58:21.680

-1

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%');

edited Nov 22 '16 at 20:58

answered Nov 22 '16 at 20:52

krokodilko

35,300
7
55
79

Add more test data! Fails with sentence "was that knot bad?". Add a space on either side of your not/no when testing for it. And "bad" for that matter as it could be part of another word also. – Gary_W Nov 22 '16 at 21:21

regular expression to exclude if preceded by not or no

2 Answers2