Cut string at last occurrence of a character in a string

Question

I have a text in a column that has information separated by "-"(hyphen) and I need to remove the last and second to last information.

Examples:

• Transform "Informacoes - Separadas - Por - Hifen" in "Informacoes - Separadas";
• Transform "Informacoes separadas - Por - Hifen" in "Informacoes separadas".

It is possible to do this only in one Select?

Answer 1

You can use SUBSTRING_INDEX to return occurrences after the delimiter, if the counter is positive returns occurrences from left to end of string, if negative returns from right.

SUBSTRING_INDEX(string,delimitador,contador)

Example:

SELECT SUBSTRING_INDEX('Informacoes - Separadas - Por - Hifen', '-', 1) as texto;

Returns:

Informacoes

Example 2:

SELECT SUBSTRING_INDEX('Informacoes - Separadas - Por - Hifen', '-', 2) as texto;

Returns:

Informacoes - Separadas 

Example 3:

SELECT SUBSTRING_INDEX('Informacoes - Separadas - Por - Hifen', '-', -2) as texto;

Returns:

 Por - Hifen 

To return Informacoes Separadas without the hyphen, you can use a combination of SUBSTRING_INDEX and CONCAT:

SELECT 
CONCAT(
SUBSTRING_INDEX(
    SUBSTRING_INDEX('Informacoes - Separadas - Por - Hifen', '-', 2),'-',1
    ),
SUBSTRING_INDEX(
    SUBSTRING_INDEX('Informacoes - Separadas - Por - Hifen', '-', 2),'-',-1
    )
) as texto;

Answer 2

You can create this function:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

And then apply it:

SELECT SPLIT_STR(informacoes, ' - ', 1) as info,
   SPLIT_STR(informacoes, ' - ', 2) as separadas
   FROM   informacoes;

I tested it now and it worked

Answer 3

The other answers do not remove the last occurrences as I needed.
They removed the first occurrences and concatenated, so they didn’t work for the second case: Informacoes separadas - Por - Hifen.

I ended up finding a solution in a old post in the Mysql references.
The solution looks for the position of the character in the inverted string, subtracts the position by the size of the string and cuts at the position found, thus removing at the last position of the desired character.

Doing it twice I got what I needed:

select 
  @string := 'Informacoes - Separadas - Por - Hifen' as String, 
  @posCorte1 := length(@string) - locate('-', reverse(@string)) AS 'Posicao corte 1', 
  @stringCortada1 := left(@string, @posCorte1) AS 'Sem ultima ocorrencia', 
  @posCorte2 := length(@stringCortada1) - locate('-', reverse(@string)) AS 'Posicao corte 2',
  substr(@string,@posCorte2 + 1) as 'Parte retirada',
  left(@string, @posCorte2) AS 'String final' 
;

The sql above solved my problem, but then I do a Function from it to facilitate.