On Wednesday, January 28, 2015 at 12:02:56 PM UTC-7, Ninja Li wrote:
>
> 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
>
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
--
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
---
You received this message because you are subscribed to the Google Groups
"Oracle PL/SQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
