Solved

Regular expression in oracle

Posted on 2012-03-09
7
600 Views
Last Modified: 2012-06-21
I need a regular expression in sql that truncates everything after the dash and including the dash.

For example:

I have 49858974-94984985
result 49858974

I have found some examples but not exactly what I need.
0
Comment
Question by:meagain0707
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 37703827
Are all the numbers first?

select regexp_substr('49858974-94984985','[0-9]*') from dual;


If this doesn't work, please provide more samples.
0
 

Author Comment

by:meagain0707
ID: 37704097
I think this will work. I will  let you know the results on Monday. Thank you.
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 250 total points
ID: 37704355
If your string is not just numbers, use this

SQL> select regexp_substr('test-this', '[^-]+')
  2  from dual;

REGE
----
test

SQL>
SQL> select regexp_substr('49858974-94984985', '[^-]+')
  2  from dual;

REGEXP_S
--------
49858974

SQL>

Open in new window

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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37704435
hmmm. do we really need regular expression? would a plain substr not be more efficient?
just curious:

select substr(yourfield, 0, instr(yourfield || '-', '-')-1) from yourtable 

Open in new window

0
 

Author Comment

by:meagain0707
ID: 37715109
Thanks angelIII

The problem with the instr code you gave me is if the number starts  with -
then nothing is returned.
The reg. exp. still retuns the number after the -.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37715133
>>if the number starts  with -

This is a new requirement.  Do you have any other requirements that you have not mentioned?

Would you not want the '-' returned?

If so try:
regexp_substr('-49858974-94984985','-?[0-9]*')
0
 
LVL 27

Expert Comment

by:sujith80
ID: 37753559
have you tried my post

SQL> select regexp_substr('-49858974-94984985', '[^-]+') from dual;

REGEXP_S
--------
49858974

SQL> select regexp_substr('49858974-94984985', '[^-]+') from dual;

REGEXP_S
--------
49858974

SQL> select regexp_substr('49858974-', '[^-]+') from dual;

REGEXP_S
--------
49858974

SQL>

Open in new window

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

762 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

21 Experts available now in Live!

Get 1:1 Help Now