Solved

Regular expression in oracle

Posted on 2012-03-09
7
605 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 77

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 143

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 77

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

830 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