sql - ORA-12728: invalid range in regular expression

Question

I want to check if valid phone number is inserting in table, so my trigger code is here:

 select start_index
              into mob_index
              from gmarg_mobile_operators
              where START_INDEX = substr(:new.contact_info,0,3);

              if (REGEXP_LIKE (:NEW.CONTACT_INFO,'^(?:'|| mob_index ||')[-,:]{0,1}[0-9][0-9][-,:]{0,1}[0-9][0-9][-,:]{0,1}[0-9][0-9]')) then
                found := 1;
              end if;

I've checked my regex: "^(?:555)[-,:]{0,1}[0-9][0-9][-,:]{0,1}[0-9][0-9][-,:]{0,1}[0-9][0-9]" on several online tools and it is correct.

When I run my trigger, it compiles successfully, but during inserting a row following error is shown:

 insert into GMARG_CONTACTS
(CLINET_ID,CONTACT_INFO,contact_type_id)
values
(0,'555194117','Mobile')
Error report -
SQL Error: ORA-12728: invalid range in regular expression
ORA-06512: at "HR.GMARG_TRIGGER_CONTACT", line 12
ORA-04088: error during execution of trigger 'HR.GMARG_TRIGGER_CONTACT'
12728. 00000 -  "invalid range in regular expression"
*Cause:    An invalid range was found in the regular expression.
*Action:   Ensure a valid range is being used.

So, if my regex is correct, why does oracle shows error? I tried to find answer, or redifine my regex, but no forward steps... thank you in advance

Answer 1

Regexp don't use to protect - in a bracket expression. You only have to put - as the first character, just after the opening bracket:

IF REGEXP_LIKE('--,,::', '[-,:]*')
...

=> ORA-12728: invalid range in regular expression

If you're curious, when encountering [-,:] Oracle understand: "any character in the range from to , or the character :". The reason why this raises an exception is appears to be after , according to their ASCII value. And Oracle don't accept range having a starting value after the ending one.

On the other hand:

 IF REGEXP_LIKE('--,,::', '[-,:]*')

Works as expected.

---

_{As a side note, [-,:]{0,1} meaning "zero or one occurrence of - or , or :" could be written [-,:]?.}