Solved

Oracle regular expression

Posted on 2016-11-02
6
47 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Identify records which do NOT qualify for a view 9 36
Encryption Decryption in Oracle 12 108
Oracle DATE Column Space 11 65
VB.Net - Oracle BulkCopy from CSV Date Format 7 47
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

863 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

19 Experts available now in Live!

Get 1:1 Help Now