Solved

Oracle regular expression

Posted on 2016-11-02
6
30 Views
Last Modified: 2016-11-03
Hi,

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.
0
Comment
Question by:sam_2012
  • 3
  • 2
6 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 41870804
Why not this:
select regexp_replace('(999) 999-999','[^0-9]') from dual;
0
 

Author Comment

by:sam_2012
ID: 41870876
i want to try with substr , it should be able to extract the 999's . any help on regexp_substr is really appreciated
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41870904
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41871203
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

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41871289
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:
Select
      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.
0
 

Author Closing Comment

by:sam_2012
ID: 41872740
awesome
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now