Remove First Name and Last Name from Full Name

Question

I am using the SUBSTRING_INDEX command to get the first name and last name of a table field "Names".

SELECT
SUBSTRING_INDEX(nome_completo, ' ', 1) AS primeiro_nome,
SUBSTRING_INDEX(nome_completo, ' ', -1) AS ultimo_sobrenome
FROM `nomes`

I just need to put all the other last names in the "middle name" field. Ex: João Pedro da Silva Souza Queiroz first name = "John" last name = "Queiroz" middle name = "Pedro da Silva Souza"

If someone knows how to do this in SQL I appreciate it (because in PHP it is easier to hehe).

Answer 1

Try something like that:

select 
SUBSTRING_INDEX(nome_completo, ' ', 1) as primeiro_nome,
SUBSTRING(nome_completo,LOCATE(' ',nome_completo) + 1,char_length(nome_completo) - LOCATE(' ', REVERSE(nome_completo)) - LOCATE(' ',nome_completo)) as nomes_do_meio,
SUBSTRING_INDEX(nome_completo, ' ', -1) as ultimo_sobrenome,
nome_completo 
from nomes;

The name João Pedro da Silva Souza Queiroz has 33 characters char_length(full name_name), the first space appears at position 5 LOCATE(' ', full name_name), the last space at position 8 provided backwards LOCATE(' ', REVERSE(full name_name)). To pick up only the characters between the first and last mirror, just pick up from the first time the space appears(position 5) and add one more, to be in position 6 LOCATE(' ',full name) + 1, then take only the total of characters without counting the first and last time the blank space appears LOCATE(' ',full name) + 1,char_length(full name) - LOCATE(' ', REVERSE(full name)) - LOCATE(' ,full name)

João Pedro da Silva Souza Queiroz
123456789012345678901234567890123
321098765432109876543210987654321

Answer 2

I believe that this is not the appropriate but functional form:

SELECT
    SUBSTRING_INDEX(nome_completo, ' ', 1) AS primeiro_nome,
    SUBSTRING_INDEX(nome_completo, ' ', -1) AS ultimo_sobrenome,
    TRIM(REPLACE(REPLACE(nome_completo,'',primeiro_nome),'',ultimo_sobrenome)) AS nomes_do_meio
FROM `nomes`

References

TRIM

REPLACE