Get Mysql name, middle name, and last name of records

Asked

Viewed 365 times

-3

How can I cut the name of a string coming from Mysql to get name, middle name and last name, I’m trying and I’m not getting, what I have so far is this, but I need the middle name and middle name to be together and separate last name

SELECT
   SUBSTRING_INDEX(SUBSTRING_INDEX(Nome, ' ', 1), ' ', -1) AS first_name,
   If(length(Nome) - length(replace(Nome, ' ', '')) > 1,  
       SUBSTRING_INDEX(SUBSTRING_INDEX(Nome, ' ', 2), ' ', -1) ,NULL) 
           as NomeMeio,
   SUBSTRING_INDEX(SUBSTRING_INDEX(Nome, ' ', 3), ' ', -1) AS ÚltimoNome
FROM cadColaborador

inserir a descrição da imagem aqui

What I need is this:

Nome: ABEL DE
Sobrenome: CAMARGO
mysql
  • 1

    How to differentiate between composed surnames and middle names?

  • If I were you, I would change the structure of your table, instead of creating a table with the fields "name, middle name and last name", create a table with only the name and last name. If the person has a middle name like "John Paul", add name in the field.

1 answer

0


I managed to do what I needed, get name, middle name and last name of records directly from a Mysql query, follow code for future searches.

SELECT
 TRIM(SUBSTRING(`cadColaborador`.Nome, 1, CHAR_LENGTH(`cadColaborador`.Nome) - 
 CHAR_LENGTH(SUBSTRING_INDEX(REVERSE(`cadColaborador`.Nome), ' ', 1)))) AS PrimeiroNome,
 REVERSE(SUBSTRING_INDEX(REVERSE(`cadColaborador`.Nome), ' ', 1)) AS UltimoNome,

  • Just one question: if the last surname is, for example, Junior, Son, Grandson, etc, you will still consider them as surname?

  • Hello @anonimo, yes, by the rules stipulated here I can consider, no problem.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.