Oracle REGEXP_LIKE function - what is this matching?

Question

I'm looking at someone else's code which I do not have the option of running and can't figure out what the following REGEXP_LIKE is trying to match. Any help would be appreciated.

REGEXP_LIKE('field_name', '^(ABC:)?Z[DEF]')

What I think is happening is as follows but I think I am wrong:

Try to match any field that:

begins with ABC: and ends D, E or F

I understand that the ^ matches the beginning of a string and that the () brackets group the expressions so is therefore grouping ABC:

However the ?Z is what is confusing me.

Any help would be appreciated, I can't seem to get my head around this no matter how many articles I read.

Answer 1

Try playing with some different strings:

with example as (select 'BC:ZDEF' as x from dual
           union select 'ABC:D' from dual
           union select 'ABC:ZE' from dual
           union select 'ZE' from dual
           union select 'ZF' from dual)
select x
from example
where REGEXP_like(x, '^(ABC:)?Z[DEF]');

Output:

x
ABC:ZE
ZE
ZF

So what's going on? You're right about ^ meaning the beginning of a line. The ? operator means the thing that comes before this is optional - it should occur 1 or 0 times. In this case, that's (ABC:), so that part of the string is optional.

Then we have a Z, which is mandatory, followed by a bracket expression, which means any single character listed between the brackets - so either D, E, or F.

So the expression means "a line starting with Z followed by D, E, or F, optionally with an "ABC:" at the beginning".