Regular expression in oracle

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.
meagain0707Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SujithConnect With a Mentor Data ArchitectCommented:
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
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Are all the numbers first?

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


If this doesn't work, please provide more samples.
0
 
meagain0707Author Commented:
I think this will work. I will  let you know the results on Monday. Thank you.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
meagain0707Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
SujithData ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.