CODEWITHSUNDEEP
regexoracle-databaseregex-negation
manic bubble
manic bubble

Reputation: 147

Oracle regexp pattern matching instances of a but not ab

I have seen from from oracle documentation I can do a|b which will match all instances of character 'a 'or character 'b'. I wondered if it was possible in regexp to have 'a' and not 'b' e.g. match all instances of 'a' except where a is followed by 'b'.

so with these 3 strings

  1. AAAA
  2. ACAD
  3. AAAB

I want to match 1 and 2 but not 3.

Upvotes: 0

Views: 116

Answers (1)

tbone
tbone

Reputation: 15473

You could try:

with x as (
  select 'AAAA' as str from dual
  union all
  select 'ACAD' as str from dual
  union all
  select 'AAAB' as str from dual
)
select * from x
where regexp_like(str, '[aA][^bB]')
and NOT regexp_like(str, '[aA][bB]')

Output:

AAAA
ACAD

Note: If you don't care if A is followed by some other non-B char, you can simply do:

with x as (
  select 'AAAA' as str from dual
  union all
  select 'ACAD' as str from dual
  union all
  select 'AAAB' as str from dual
)
select * from x
where regexp_like(str, '[aA]')
and NOT regexp_like(str, '[aA][bB]')

which would match "AAAA" or "XYZA"

Upvotes: 1

Related Questions