We help IT Professionals succeed at work.

Regular expression in oracle

meagain0707
meagain0707 asked
on
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.
Comment
Watch Question

SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Are all the numbers first?

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


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

Author

Commented:
I think this will work. I will  let you know the results on Monday. Thank you.
Data Architect
BRONZE EXPERT
Commented:
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

Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Author

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 -.
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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]*')
SujithData Architect
BRONZE EXPERT

Commented:
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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.