Solved

Oracle regular expression

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

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

773 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