Hi,
I've got a column that can contain up to 3 parts:
1. anything up to the first underscore
2. anything between the first and second underscore (if there isn't a second underscore, then it's from the first underscore to the end of the string)
3. anything after the third underscore, which could include other underscores.
The first two parts are easy to do, but I've struggled to find a REGEXP_SUBSTR way of doing the third part, and have ended up with a clunky substr / instr combo. Is there a better way?
with sd as (select 'abcd_1234' str from dual union all
select 'abcd_1234_1' str from dual union all
select 'abcd_1234_efg_1' str from dual union all
select 'abcd_1234_efg_hij_2' str from dual)
select str,
REGEXP_SUBSTR(str, '[^_]+', 1, 1) str_1,
REGEXP_SUBSTR(str, '[^_]+', 1, 2) str_2,
SUBSTR(str, DECODE(INSTR(str, '_', 1, 2), 0, NULL, INSTR(str, '_', 1, 2) + 1)) str_3
from sd;
STR STR_1 STR_2 STR_3
------------------- ------------------- ------------------- -------------------
abcd_1234 abcd 1234
abcd_1234_1 abcd 1234 1
abcd_1234_efg_1 abcd 1234 efg_1
abcd_1234_efg_hij_2 abcd 1234 efg_hij_2
TIA!
ETA: I'm on 10.2.0.4
Edited by: Boneist on 09-Aug-2011 17:30