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.

Barbaros Özhan · Accepted Answer · 2020-08-17 18:00:58Z

1

underscores might be counted REGEXP_COUNT(str,'_') - 1 times instead of REGEXP_COUNT(str,'_') + 1, which is the number of substrings splitted by underscores, and concatenate all by using LISTAGG(...,'_')... function in order to get the substring upto the penultimate underscore :

WITH t(ID,str) AS
(
 SELECT 1, 'LORIK_BB_ID_FF_KKK_HUY_222_44' FROM dual UNION ALL
 SELECT 2, 'LUN_GGG_MAMA_FF_GG_GG_TT22_3'  FROM dual 
)  
 SELECT ID,
        LISTAGG(REGEXP_SUBSTR(str,'[^_]+',1,level),'_')
        WITHIN GROUP (ORDER BY level) AS "Result String"
   FROM t
CONNECT BY level <= REGEXP_COUNT(str,'_') - 1
    AND PRIOR SYS_GUID() IS NOT NULL
    AND PRIOR ID = ID
  GROUP BY ID;


ID  Result String
--  ----------------------
1   LORIK_BB_ID_FF_KKK_HUY
2   LUN_GGG_MAMA_FF_GG_GG

Demo

edited Aug 17, 2020 at 18:00

answered Aug 17, 2020 at 17:50

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Comments

Gary_W · Accepted Answer · 2020-08-17 20:25:46Z

REGEXP_SUBSTR could be used to divide the string into 2 parts using groups. The first group being the string up to but not including the penultimate underscore, the second group being the penultimate underscore and the rest of the line until the end (allowing for any number of characters between the underscores). Return the 1st group.

WITH T(ID,str) AS
(
 SELECT 1, 'LORIK_BB_ID_FF_KKK_HUY_222_44' FROM dual UNION ALL
 SELECT 2, 'LUN_GGG_MAMA_FF_GG_GG_TT22_3'  FROM dual 
)  
SELECT ID,
REGEXP_SUBSTR(str, '(.*)(_.*_.*$)', 1, 1, NULL, 1) mystring
from t;

        ID MYSTRING                     
---------- -----------------------------
         1 LORIK_BB_ID_FF_KKK_HUY       
         2 LUN_GGG_MAMA_FF_GG_GG        

2 rows selected.

Atif · Accepted Answer · 2020-08-17 15:12:47Z

0

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;

answered Aug 17, 2020 at 15:12

Atif

2,2202 gold badges11 silver badges27 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related