0

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.

manic bubble
  • 147
  • 1
  • 3
  • 13
  • doesn't #3 (AAAB) match where A is followed by a non-B? (the first AA part) – tbone Nov 15 '17 at 19:29
  • 'a' and not 'b' is quite different from "except where 'a' is **followed** by 'b'". The string 'ba' does not satisfy the condition [ 'a' and not 'b' ] because it does have a 'b', but it does not come **after** 'a'. Please clarify. Also, if you really meant "follow" - does that mean **immediately** follow, or does it mean "follow *somewhere* later in the string, not necessarily **immediately** after 'a'"? –  Nov 15 '17 at 20:26
  • 2
    You just need to check if there is no `AB` in the string, `col NOT LIKE '%AB%' AND col LIKE '%A%'` – Wiktor Stribiżew Nov 15 '17 at 20:29

1 Answers1

1

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"

tbone
  • 15,107
  • 3
  • 33
  • 40
  • The first condition in WHERE is not excessive; the OP didn't say he needs an A followed by something else. Indeed, the A can be at the end of the string (or even, the entire string can be 'A'). –  Nov 15 '17 at 20:24