Solved

separate column

Posted on 2016-11-15
24
20 Views
Last Modified: 2016-11-16
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
Comment
Question by:enrique_aeo
  • 11
  • 5
  • 3
  • +2
24 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41888770
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
 

Author Comment

by:enrique_aeo
ID: 41888785
col3: Mendoza Ponce = lastname
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41888791
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41888797
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
 

Author Comment

by:enrique_aeo
ID: 41888804
The data comes from SAP and according to them the names and surnames have it in a single field
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41888805
>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
 

Author Comment

by:enrique_aeo
ID: 41888806
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41888809
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
 

Author Comment

by:enrique_aeo
ID: 41888817
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
 

Author Comment

by:enrique_aeo
ID: 41888820
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
 

Author Comment

by:enrique_aeo
ID: 41888822
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41889006
@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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41889307
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
 

Author Comment

by:enrique_aeo
ID: 41889624
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41889630
What about last 2 column, how many words should go in col2 and col3 ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41889633
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
 

Author Comment

by:enrique_aeo
ID: 41890033
always shows 4 names
I am 100% sure
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41890037
Can you give us 2 rows and the expected output.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41890112
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
 

Author Comment

by:enrique_aeo
ID: 41890427
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41890440
Yes.  That was just to show the results on the sample data you posted.

SELECT ...
FROM dbo.your_table_name
CROSS APPLY ...
0
 

Author Comment

by:enrique_aeo
ID: 41890510
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 41890519
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
 

Author Comment

by:enrique_aeo
ID: 41890529
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now