CODEWITHSUNDEEP
sqloracle-database
Javi Torre
Javi Torre

Reputation: 824

Regexp substr until underscore + digit

I have the this string: 'STRING_EXA2MP_3LE'. I want to obtain all the characters until there is an underscore followed by a digit, which in this case would output 'STRING_EXA2MP'. How could I obtain this?

This is what I have tried so far.

SELECT
    regexp_substr('STRING_EXA2MP_3LE', '[^(_0-9]+', 1, 1)
FROM
    dual

Upvotes: 0

Views: 80

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

I suggest

SELECT regexp_substr('STRING_EXA2MP_3LE', '(.+)_[0-9]+', 1, 1, NULL, 1)
  FROM dual

The subexpression in parentheses ((.+)), which is subexpression #1 (important later) says "Match 1 or more of any character". The rest of the expression (_[0-9]+) says "Match an underscore followed by one or more numeric digits". The last argument to REGEXP_SUBSTR says "Return the value of subexpression #1". So using the subexpression here is important as it allows you to extract a portion of the matched string.

db<>fiddle here

Upvotes: 1

Pugzly
Pugzly

Reputation: 934

Select regexp_replace('STRING_EXA2MP_3LE', '_[0-9].*') from dual

Upvotes: 1

oisene
oisene

Reputation: 211

Try this:

SELECT regexp_substr('STRING_EXA2MP_3LE', '(.+_?)_[0-9]?', 1, 1,'i',1)
FROM dual;

Upvotes: 0

Related Questions