Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Regular expression help for matching numbers

ash0602Aug 6 2010 — edited Aug 6 2010
Hi,

I want a exact match of either 9 digits or 12 digits, my query should give "No Match Found" as the input value is actually 10 digit

select case when regexp_like(regexp_replace( ' 123 4567 890', ' ' ), '^([0-9]{9})|([0-9]{12})$')
then 'Match Found'
else 'No Match Found'
end as test
from dual;

Need help, as I must be doing something very basic thing, wrong.

Regards,
Ash
This post has been answered by Hoek on Aug 6 2010
Jump to Answer

Comments

Hoek
Answer
Remove 2 brackets:
SQL> select case when regexp_like(regexp_replace( ' 123 4567 890', ' ' ), '^([0-9]{9}|[0-9]{12})$')
  2  then 'Match Found'
  3  else 'No Match Found'
  4  end as test
  5  from dual;

TEST
--------------
No Match Found

SQL> select case when regexp_like(regexp_replace( ' 123 4567 89', ' ' ), '^([0-9]{9}|[0-9]{12})$')
  2  then 'Match Found'
  3  else 'No Match Found'
  4  end as test
  5  from dual;

TEST
--------------
Match Found
SQL>
Marked as Answer by ash0602 · Sep 27 2020
737905 
SQL> var num number;

SQL> exec :num:=1234567890

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  SELECT (CASE WHEN LENGTH(:num) =9 OR LENGTH(:num) = 12 THEN 'MATCH FOUND' ELSE 'MATCH NOT FOUND
  2* from dual
SQL> /

TEST
---------------
MATCH NOT FOUND

SQL> exec :num:=123456789

PL/SQL procedure successfully completed.

SQL> /

TEST
---------------
MATCH FOUND

SQL>
1 person found this helpful
ash0602
This is too fast a reply, and thanks for correcting my basic understanding. The same expression (my old) will work for javascript, but there is this slight difference between the handling regular expression in database and UI :)

Cheers,
Ash
Aketi Jyuuzou
Umm. Regex of Oracle has not supprted "Tanaka Akira Special" yet. :-(
with t(Val) as(
select ' 123 4567 890' from dual union all
select ' 123 4567 89'  from dual)
select Val,
case when RegExp_Like(replace(Val,' '),'^[0-9]{9}([0-9]{3})?$')
     then 1 else 0 end as result
  from t;

VAL            RESULT
-------------  ------
 123 4567 890       0
 123 4567 89        1
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 3 2010
Added on Aug 6 2010
#plsql, #sql
4 comments
3,658 views