Oracle Remove part of string after a specific occurrence of a character

Question

I was trying to obtain a string before the penultimate occurrence of '_' character:

For instance:

 LORIK_BB_ID_FF_KKK_HUY_222_44
 LUN_GGG_MAMA_FF_GG_GG_TT22_3

Obtain this part (considering that the string will always have 4 or more underlines and the same amount of underlines for each record):

LORIK_BB_ID_FF_KKK_HUY
LUN_GGG_MAMA_FF_GG_GG

I already tried using:

select SUBSTR('LORIK_BB_ID_FF_KKK_HUY',0, (INSTR ('LORIK_BB_ID_FF_KKK_HUY', '_', -1)) - 1) from dual;

This one seems to obtain every character before the last occurence of '_', I can't seem to find a solution to obtain the characters before the penultimate occurence of underscore.

Answer 1

Why you are not trying this:

 select SUBSTR('LUN_GGG_MAMA_FF_GG_GG_TT22_3',0, (INSTR ('LUN_GGG_MAMA_FF_GG_GG_TT22_3', '_', -1, 2)) - 1) from dual;

select SUBSTR('LORIK_BB_ID_FF_KKK_HUY_222_44',0, (INSTR ('LORIK_BB_ID_FF_KKK_HUY_222_44', '_', -1, 2)) - 1) from dual;