How to separate the words from the column of the table according to the space between them through the query (Select format) without making any changes to the table.
In this specific case I seek to separate the name, father's last name and mother's last name
example:
Juan Peterson Checo
-Juan
-Peterson
-Checo
I would solve it like this. Having something like this this:
As you do not say in which database engine I did it in MsSql Server
Generally speaking, I think you need a pattern to separate the components of the chain. I used the blank space. And what is more complicated is knowing what names and surnames are and how many are. It's probably the same pattern in all the records so you just have to accommodate it
Use the PARSENAME and REPLACE functions in the code part. Who are the ones who actually do the work, it would be a matter of verifying the equivalents in the database engine in case Microsoft Sql Server is different
SELECT PARSENAME(REPLACE(Nombre,' ','.'),3) AS PrimerNombre,
PARSENAME(REPLACE(Nombre,' ','.'),4) AS SegundoNombre,
PARSENAME(REPLACE(Nombre,' ','.'),2) AS PrimerApellido,
PARSENAME(REPLACE(Nombre,' ','.'),1) AS SegundoApellido
FROM [Borreme].[dbo].[Personas]Although I think this task should be done on the backend side, be it in PHP, JS, Java, JS, whatever it is here is my solution: a function that solves the problem and you can reuse it for more cases, instead to have your 'dirty' select:
Function
CREATE FUNCTION split(val VARCHAR(255), del VARCHAR(255), pos INT)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(val, del, pos),LENGTH(SUBSTRING_INDEX(val, del, pos-1)) + 1), del, '');
Exit
SELECT split('Hola StackOverflow en español', ' ', 1); # 'Hola'
SELECT split('Hola StackOverflow en español', ' ', 2); # 'StackOverflow'
SELECT split('Hola StackOverflow en español', ' ', 3); # 'en'
SELECT split('Hola StackOverflow en español', ' ', 4); # 'español'
Try this select to get the first and last name of the "name" field of the "users" table
select left(nombre, CHARINDEX(' ', nombre)) as nombre_usuario,
substring(nombre, CHARINDEX(' ', nombre)+1, len(nombre)-(CHARINDEX(' ',
nombre)-1)) as apellido_usuario
from usuarios