Oracle regular expression alternate possibilities

[Ninja Li]

Hi,

 

I am trying to test Oracle regular expressions on Oracle 11g 2.0.3.0 and I am having an issue with the following query to check phone numbers:

 

select regexp_substr('(666)888-0000','[0-9]{3}|\([0-9]{3}\)') PHONE_1,
          regexp_substr('666(888)-0000','[0-9]{3}|\([0-9]{3}\)') PHONE_2
from   dual;

 

The query will return the following:

 

PHONE_1       PHONE_2     

(666)                   666

 

My question is about the return value of PHONE_1. I was expecting the query to return "666" for PHONE_1 instead of "(666)". After I changed the number from '(666)888-0000' to ' 666(888)-0000', I am getting the expected result for PHONE_2.

 

Please advise on this.

 

Thanks in advance.

 

Nick Li

[ddf]

You're giving regexp_substr the option of returning either string as it finds it; you really need to simplify the code to get the results you want:

 SQL>  select regexp_substr('(666)888-0000','[0-9]{3}') PHONE_1,
  2            regexp_substr('666(888)-0000','[0-9]{3}') PHONE_2
  3  from   dual;

PHO PHO
--- ---
666 666

SQL>

Sometimes simpler is better.

David Fitzjarrell

[Ninja Li]

David,

Thanks for your kind help and that explains it. 

I really appreciated it.

Nick Li