Solved

Oracle regular expression

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

Title # Comments Views Activity
selective queries 7 37
ORA-02288: invalid OPEN mode 2 56
SYS password changed. Now can't log in as SYS 27 28
oracle forms question 22 38
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

766 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