• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 52
  • Last Modified:

separate column

Hi experts

i have this column: [Nombre_empleado]
Segundo Alfredo Mendoza Ponce

i need separate in three columns
col1: Segundo
col2: Alfredo
col3: Mendoza Ponce

the data is:
Segundo Alfredo Mendoza Ponce
Nelson Eduardo Alvarado Gomez
Ana Lucia Cordoba Gomez
0
enrique_aeo
Asked:
enrique_aeo
  • 11
  • 5
  • 3
  • +2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Curiosity overwhelms me ... What would be the logic to know to keep 'Mendoza Ponce' in one column vs. the separate 'Segundo' and 'Alfredo' in separate columns?
0
 
enrique_aeoAuthor Commented:
col3: Mendoza Ponce = lastname
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Okay, but this is a common problem with coding where first and last names coexist in the same column, and some names are two words.  How would SQL Server know which is which?

I have friends with these names ... Can you program T-SQL to tell the difference between first name and last name every time?
  • Hans Jurgen Von Heteren-Freeze
  • Clarence Der Schleickmeister
  • Jean Claude Van Damme
  • Rush Hudson Limbaugh III
  • Wendy Sue Pokipsee
  • Bill S. Preston, Esquire
  • Ted Theodore Logan
  • Buddy Holly  (both can be an acceptable first name, so if in a list it'll be tough to know how to assign it correctly)

I bring this up because many times the answer is 'You can't code SQL Server well enough to know every time, which means you're going to have make some on-the-fly assumptions.

Setting that aside, check out T-SQL:  Normalized data to a single comma delineated string and back, scroll down to the 'Comma delineated string to normalized data' section, which after you substitute space for comma will at least get you to the point where all names are in separate columns.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Also, if your company is willing to throw a little money at this problem, I know there are third-party services which will have a big list of common first and last names, which will allow you to run your dataset through the service and get you about 99.??% of the way there.

I don't remember the names of them though, sorry, but I'm guessing they can be found via Google using a search like 'Data Quality Names'
0
 
enrique_aeoAuthor Commented:
The data comes from SAP and according to them the names and surnames have it in a single field
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>according to them the names and surnames have it in a single field
In that case you have my permission to locate whomever designed that so that first and last names are in the same column, thus giving you this problem, and give them a swift slap in the back of the head.

>The data comes from SAP
Surprising.  They should know better.
0
 
enrique_aeoAuthor Commented:
i mystake

i have this column: [Nombre_empleado]
 Segundo Alfredo Mendoza Ponce

 i need separate in three columns
 col1: Mendoza Ponce
 col2: Segundo
 col3: Alfredo
please help
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Same answer.  Also I'm going to go with the correct answer of 'You can't do that' here, other than the >80% solution I posted above with the article link, and the 99.??% solution of the third-party service.
0
 
enrique_aeoAuthor Commented:
this article
https://www.experts-exchange.com/questions/28983290/separate-column.html
It does not help me because I have the data separated by a space
0
 
enrique_aeoAuthor Commented:
I am sure that the first 2 values should go in the first column

[Nombre_empleado]
Bardales Apolo Cyndi Yolanda

  i need separate in three columns
  col1: Bardales Apolo
  col2: Cyndi
  col3: Yolanda
0
 
enrique_aeoAuthor Commented:
for example
Bardales Apolo Cyndi Yolanda
Alvarez Calderon Sattui Gonzalo
Martinez Ore Gabriel Daniel
Lopez Arroyo Eli Egberto

then col1:
Bardales Apolo
Alvarez Calderon
Martinez Ore
Lopez Arroyo
0
 
Pawan KumarDatabase ExpertCommented:
@Author -

Jim Sir is right.

How we can decide whether we need pick first word or 2 words for the first Name. ? In T-SQL we have build logic based on the requirement.Here we dont know how many words we need to pick for first name
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That only works for Latino names. Is typical for a Latino to have 4 names (2 first names and 2 last names) and in some occasions even more.
So Enrique is it possible to have a name like this: Antonio Eduardo Banderas Gil y Gil ? If affirmative the result should be like:
col1: Banderas Gil y Gil
col2: Antonio
col3: Eduardo


Is this assumption right?
0
 
enrique_aeoAuthor Commented:
I am 100% sure that the first 2 values should go in the first column

 [Nombre_empleado]
 Bardales Apolo Cyndi Yolanda

   i need separate in three columns
   col1: Bardales Apolo
   col2: Cyndi
   col3: Yolanda
0
 
Pawan KumarDatabase ExpertCommented:
What about last 2 column, how many words should go in col2 and col3 ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Enrique, your examples always shows 4 names but what we want to know is how to deal with exceptions (for example people with 3 or 5 names).
0
 
enrique_aeoAuthor Commented:
always shows 4 names
I am 100% sure
0
 
Pawan KumarDatabase ExpertCommented:
Can you give us 2 rows and the expected output.
0
 
Scott PletcherSenior DBACommented:
I've used CROSS APPLYs to remove the "find the spaces" logic from the main SELECT, for less "clutter" in the main SELECT.

If/when you run into exceptions, let me know and we can adjust the code as needed then.


SELECT Nombre_empleado,
    LEFT(Nombre_empleado, col1_end - 1) AS col1,
    SUBSTRING(Nombre_empleado, col1_end + 1, col3_start - col1_end - 2) AS col2,
    SUBSTRING(Nombre_empleado, col3_start, 100) AS col3
FROM (
    VALUES('Segundo Alfredo Mendoza Ponce'),('Nelson Eduardo Alvarado Gomez'),('Ana Lucia Cordoba Gomez'),('Segundo Alfredo Mendoza Ponce')
) AS test_data(Nombre_empleado)
CROSS APPLY (
    SELECT CHARINDEX(' ', Nombre_empleado, CHARINDEX(' ', Nombre_empleado) + 1) AS col1_end
) AS ca1
CROSS APPLY (
    SELECT CHARINDEX(' ', Nombre_empleado, col1_end + 1) + 1 AS col3_start
) AS ca2
0
 
enrique_aeoAuthor Commented:
this is the results

Nombre_empleado                             col1                             col2                     col3
Segundo Alfredo Mendoza Ponce      Segundo Alfredo      Mendoza      Ponce
Nelson Eduardo Alvarado Gomez      Nelson Eduardo      Alvarado      Gomez
Ana Lucia Cordoba Gomez      Ana Lucia      Cordoba      Gomez
Segundo Alfredo Mendoza Ponce      Segundo Alfredo      Mendoza      Ponce

this line be replaced by the table?
VALUES('Segundo Alfredo Mendoza Ponce'),('Nelson Eduardo Alvarado Gomez'),('Ana Lucia Cordoba Gomez'),('Segundo Alfredo Mendoza Ponce')
0
 
Scott PletcherSenior DBACommented:
Yes.  That was just to show the results on the sample data you posted.

SELECT ...
FROM dbo.your_table_name
CROSS APPLY ...
0
 
enrique_aeoAuthor Commented:
SELECT Nombre_empleado,
     LEFT(Nombre_empleado, col1_end - 1) AS col1,
     SUBSTRING(Nombre_empleado, col1_end + 1, col3_start - col1_end - 2) AS col2,
     SUBSTRING(Nombre_empleado, col3_start, 100) AS col3
 FROM [dbo].[empleados]
 CROSS APPLY (
     SELECT CHARINDEX(' ', Nombre_empleado, CHARINDEX(' ', Nombre_empleado) + 1) AS col1_end
 ) AS ca1
 CROSS APPLY (
     SELECT CHARINDEX(' ', Nombre_empleado, col1_end + 1) + 1 AS col3_start
 ) AS ca2

i have this error
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
0
 
Scott PletcherSenior DBACommented:
Then what you posted here:

always shows 4 names
I am 100% sure

isn't true.


SELECT Nombre_empleado,
    LEFT(Nombre_empleado, col1_end - 1) AS col1,
    CASE WHEN col3_start = 1 THEN '' ELSE SUBSTRING(Nombre_empleado, col1_end + 1, col3_start - col1_end - 2) END AS col2,
    CASE WHEN col3_start = 1 THEN '' ELSE SUBSTRING(Nombre_empleado, col3_start, 100) END AS col3
FROM (
    VALUES('Segundo Alfredo Mendoza Ponce'),('Nelson Eduardo Alvarado Gomez'),('Ana Lucia Cordoba Gomez'),('Segundo Alfredo Mendoza Ponce'),
        ('a b'),('a')
) AS test_data(Nombre_empleado)
CROSS APPLY (
    SELECT CHARINDEX(' ', Nombre_empleado + ' ', CHARINDEX(' ', Nombre_empleado) + 1) AS col1_end
) AS ca1
CROSS APPLY (
    SELECT CHARINDEX(' ', Nombre_empleado + ' ', col1_end + 1) + 1 AS col3_start
) AS ca2
0
 
enrique_aeoAuthor Commented:
it's works
SELECT Nombre_empleado,
     LEFT(Nombre_empleado, col1_end - 1) AS col1,
     CASE WHEN col3_start = 1 THEN '' ELSE SUBSTRING(Nombre_empleado, col1_end + 1, col3_start - col1_end - 2) END AS col2,
     CASE WHEN col3_start = 1 THEN '' ELSE SUBSTRING(Nombre_empleado, col3_start, 100) END AS col3
 FROM [dbo].[empleados]
 CROSS APPLY (
     SELECT CHARINDEX(' ', Nombre_empleado + ' ', CHARINDEX(' ', Nombre_empleado) + 1) AS col1_end
 ) AS ca1
 CROSS APPLY (
     SELECT CHARINDEX(' ', Nombre_empleado + ' ', col1_end + 1) + 1 AS col3_start
 ) AS ca2
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 11
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now