I am trying to get the remaining string (from right) after x number of a specific character... ex:
D-ERT-ESTTE
D-EST-AER-EJEL
D-E-AD
I would like to get all string data after the second '-'
Results Expected:
ESTTE
AER-EJEL
AD
I have tried modifying substring(SKU,1,regexp_instr(SKU,'-',1,2)-1)
, however this is only giving me giving me everything to the left of the second '-'... I need from the right though
Update: Looks like maybe the below works:
substr(SKU,regexp_instr(SKU,'-',1,2)+1)
try this
select fld1, SPLIT_PART(fld1,'-',3), substr(fld1,regexp_instr(fld1,'-',1,2)+1), regexp_instr(fld1,'-',1,2) from (
select 'D-ERT-ESTTE' fld1 from dual union all
select 'D-EST-AER-EJEL' from dual union all
select' D-E-ADF' from dual );
I like @hkandpal solution that looks first for the index of the second character, and then gets the substring out.
Presenting this as a regex-only alternative - that extracts the first group that matches after the two characters are seen. The regex is [^-]*-[^-]*-(.*):
select fld1, regexp_substr(fld1, '[^-]*-[^-]*-(.*)', 1, 1, 'c', 1)
from (
select 'D-ERT-ESTTE' fld1 union all
select 'D-EST-AER-EJEL' union all
select' D-E-ADF'
);
