Get the part of the string up to the second to last occurrence of a character-CodePudding

I have the below strings

'HOLA1_HOLA2_HOLA3_HOLA4'
'HOLA1_HOLA2_HOLA3_HOLA4_HOLA5'

How could I get the part of the string up to the second to last occurrence of the '_' character?

Expected result:

'HOLA1_HOLA2'
'HOLA1_HOLA2_HOLA3'

CodePudding user response：

Use simple (fast) string functions and find the substring up to the second-to-last underscore (rather than using (slow) regular expressions):

SELECT SUBSTR(value, 1, INSTR(value, '_', -1, 2) - 1) AS first_part
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'HOLA1_HOLA2_HOLA3_HOLA4' FROM DUAL UNION ALL
SELECT 'HOLA1_HOLA2_HOLA3_HOLA4_HOLA5' FROM DUAL;

Outputs:

FIRST_PART
HOLA1_HOLA2
HOLA1_HOLA2_HOLA3

fiddle

CodePudding user response：

Regarding a regular expression with this behaviour: You can use a lookahead to get the expected Result

.*(?=_)

A lookahead is a zero length assertion (is not included in the match) that asserts that a match has to be followed by the given expression ( _ in this case). By default regex does a greedy match, therefore the lookahead targets the last underscore in the given text. Try it here: regex101