Cut string on the last occurrence of a character in a string

4

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

Examples:

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

Is it possible to do this only in a Select ?

    
asked by anonymous 30.05.2016 / 22:42

3 answers

1

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

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

Doing this 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 make a function from it to make it easier.

    
06.06.2016 / 14:25
3

You can use SUBSTRING_INDEX to return the occurrences after the delimiter, if the counter is positive returns the occurrences counting from left to the end of the string, if negative returns from the 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;
    
30.05.2016 / 23:13
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, '');

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.

    
30.05.2016 / 22:53