• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

Oracle regular expression


Below is the sql query which i have written to extract only the digits  999999999 from the input string

Select regexp_substr('(999) 999-999','[^/(][[:digit:]]{3}[^/)] [[:digit:]]{3}[^/-][[:digit:]]{3}') from dual.

Expected answer is 999999999 but iam not getting the results. Any help is really appreciated.
  • 3
  • 2
1 Solution
slightwv (䄆 Netminder) Commented:
Why not this:
select regexp_replace('(999) 999-999','[^0-9]') from dual;
sam_2012Author Commented:
i want to try with substr , it should be able to extract the 999's . any help on regexp_substr is really appreciated
slightwv (䄆 Netminder) Commented:
I don't think you can get what you want with substring.  Well, you probably can but it would be MUCH more involved that what you posted.

Substring does just that:  returns a substring from a larger string.  It will return the substring that matches the pattern you provided.

I don't believe there is a regex pattern that says "get these"..."ignore all these"..."get these".

If you look at your starting string you have 3 substrings that are all numbers.

I know of no way to "ignore" everything else with substring.
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Geert GOracle dbaCommented:
actually ... you can do it with substr, but it's so silly ...
i have to admit, i used slightwv's code

select regexp_substr(regexp_replace('(999) 999-999','[^0-9]'), '.*')  from dual;

Open in new window

slightwv (䄆 Netminder) Commented:
The regexp_replace is the correct solution to your problem.

I "can" push in a thumb-tack with a sledge hammer but why would you?

I can beat Geert's and do it without the regexp_replace.

As long as you know you have a maximum of 3 groups of numbers:
      regexp_substr('(999) 999-999','[0-9]+',1) ||
      regexp_substr('(999) 999-999','[0-9]+',2) ||
      regexp_substr('(999) 999-999','[0-9]+',3)
from dual

It breaks as soon as you add a 4th or more groups.
sam_2012Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now