Split the string in oracle query using regexp_substr

Question

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)?

Tim Biegeleisen · Accepted Answer · 2018-07-24 14:01:18Z

1

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;

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.

edited Jul 24, 2018 at 14:01

answered Jul 24, 2018 at 13:55

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chrᴉz remembers Monica Over a year ago

+ this is much simpler than regex. I strongly guess more ppl learn string manipulation functions than ppl learning oracle regex.

Tim Biegeleisen Over a year ago

@Chrᴉz It's more of a performance thing; there are many instances where using a regex engine is perfectly OK. But, we don't have to use it just because it's available.

Collectives™ on Stack Overflow

Split the string in oracle query using regexp_substr

1 Answer 1

Demo

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related