Link to home
Create AccountLog in
Avatar of meagain0707
meagain0707

asked on

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.
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of meagain0707
meagain0707

ASKER

I think this will work. I will  let you know the results on Monday. Thank you.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

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 -.
>>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]*')
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