CODEWITHSUNDEEP
regexsubstr
Selva
Selva

Reputation: 15

Split the string in oracle query using regexp_substr

I have a string like R09801E_ZJDE0001. I am able to split the first part of the string, the part before _ (R09801E), by using below systax

regexp_substr('R09801E_ZJDE0001', '[^_]+', 1, 1)

Can anyone please suggest how to get the second part of the string after the _ (ZJDE0001)?

Upvotes: 0

Views: 1102

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

You can actually do this with the base string functions:

SELECT
    SUBSTR(col, 1, INSTR(col, '_') - 1) AS first_part,
    SUBSTR(col, INSTR(col, '_') + 1) AS second_part
FROM yourTable;

enter image description here

Demo

The reason I suggest this approach is that using SUBSTR with INSTR would likely outperform a regex based solution.

If you really want to use a regex approach, then I recommend using REGEXP_REPLACE:

SELECT REGEXP_REPLACE('R09801E_ZJDE0001', '.*_', '') AS second_part
FROM dual;

This would strip off everything coming before, up to and including, the underscore, which leaves us with the second part.

Upvotes: 1

Related Questions