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

SQL to separate a field into two pieces based on a comma

I need to bring apart a field containing supervisor names into two fields containing the supervisors first name and last name.  The current concatenated field uses a comma to separate the two values.  How can I write a query that will give me the first name and last name of everyone in the field as two separate values?

Sample Data:
Smith, John
Jones, Andrew

Needed Result:  (two fields)
Last Name              First Name
Smith                      John
Jones                       Andrew

I have this query that gives me their last names, but it also includes the comma, which I don't need, but I'm not able to figure out how to drop the comma or get their first names.

select LEFT(p_supervis, charindex(',', p_supervis)) as p_supfname from persnl

Open in new window

0
fcsIT
Asked:
fcsIT
  • 4
  • 3
  • 3
  • +2
4 Solutions
 
CluskittCommented:
SELECT LEFT(p_supervis, CHARINDEX(',',p_supervis)-1) AS LastName, SUBSTRING(p_supervis,CHARINDEX(','.p_supervis)+2,LEN(p_supervis))
FROM persnl
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You need to do a -1 to remove the comma.  Here you go.

-- Populate a temp table with a couple of names
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp

CREATE TABLE #tmp (name varchar(100))

INSERT INTO #tmp (name) VALUES ('Smith, John'), ('Jones, Andrew')

-- Get rid of any leading or trailing spaces
UPDATE #tmp
SET name = LTRIM(RTRIM(name))

-- Separate first and last names
select 
	LEFT(name, charindex(',', name) - 1) as last_name, 
	RIGHT(name, len(name) - charindex(',', name) - 1) as first_name
from #tmp 

Open in new window

0
 
LIONKINGCommented:
Try something like this:

SELECT
LastName=SUBSTRING(Name,1,CHARINDEX(',',Name,1)-1),
FirstName=SUBSTRING(Name,CHARINDEX(',',Name,1)+1,LEN(Name))
FROM Names

Change your table and column names accordingly.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
lwadwellCommented:
try
select LEFT(p_supervis, charindex(',', p_supervis)-1) as p_suplname 
       SUBSTRING(p_supervis, charindex(',', p_supervis)+1,999) as p_supfname 
from persnl

Open in new window

You may need to ltrim and rtrim to remove leading/trailing spaces.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Also, most data sources will not be a perfect lastname{comma}{space}firstname due to data entry, so you may have to handle these situations separately:

   Some will have a middle initial, some not
   Some may have a multiple-initial name, such as 'Griffin, W.E.B.' or 'Griffin, W E B'
   Some may have a space in the last name, such as 'Van der Sloot, Hans'
   Some may have a suffix, such as 'Griffin, Robert III'
   There may be NULL values in the name field which you'd need to weed out.

This is by no means a complete list.
0
 
fcsITAuthor Commented:
Wow, thanks for the awesomely quick response all of you!  Unfortunately, every query offered here failed to run, each failing with different errors.

All of the queries, except Cluskitt's, failed, saying they had an "Invalid length parameter passed to the LEFT or SUBSTRING function."

Cluskitt's query failed saying "The charindex function requires 2 or 3 arguments."

:(
0
 
fcsITAuthor Commented:
Thanks jimhorn, you're exactly right.  The list will be reviewed by HR, who will make corrections as needed.  This is being used for data conversion to a new system.  We have to dump out a lot of our existing data to spreadsheets to load it into the new system.  The data in the spreadsheets is being evaluated before being sent to the new vendor.
0
 
LIONKINGCommented:
That means that there must be records that don't have a comma.
Like jimhorn said, this list should be reviewed first and all "special" cases should be considered in your query.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>every query offered here failed to run
Please tell us what you are using to run these.  My T-SQL executes in both SSMS 2008 R2 and 2012 fine.
0
 
CluskittCommented:
Mine had a - in the second charindex. It was a typo. If you replace it with a comma, it should work as the others.

All queries will generate an error if you have any field that has no comma. To prevent this, you can do something like:
SELECT CASE WHEN CHARINDEX(',',p_supervis)>0 THEN LEFT(p_supervis, CHARINDEX(',',p_supervis)-1) ELSE '' END AS LastName, CASE WHEN CHARINDEX(',',p_supervis)>0 THEN SUBSTRING(p_supervis,CHARINDEX(',',p_supervis)+2,LEN(p_supervis)) ELSE p_supervis END FirstName
FROM persnl
0
 
fcsITAuthor Commented:
Yes, there are some with no values, not null, just empty.  Ok.  I'll see if I can get the desired result using a formula in Excel.

Thanks guys.  I'll split up the points evenly.

Steve
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Small addition to the above post, to weed out NULL values and any values without a comma:

-- Populate a temp table with a couple of names
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp

CREATE TABLE #tmp (name varchar(100))

INSERT INTO #tmp (name) 
VALUES	
	('Smith, John'), ('Jones, Andrew'), (NULL), ('Flash Jack')

-- Get rid of any leading or trailing spaces
UPDATE #tmp
SET name = LTRIM(RTRIM(name))

-- Separate first and last names
select 
	LEFT(name, charindex(',', name) - 1) as last_name, 
	RIGHT(name, len(name) - charindex(',', name) - 1) as first_name
from #tmp 
WHERE name IS NOT NULL
	AND CHARINDEX(',', name) <> 0

SELECT 
	LEFT(name, CHARINDEX(',',name)-1) AS LastName, 
	SUBSTRING(name,CHARINDEX(',', name)+2,LEN(name))
FROM #tmp 
WHERE name IS NOT NULL
	AND CHARINDEX(',', name) <> 0

Open in new window

0
 
CluskittCommented:
The best option, though, would be to search for fields that have no comma and fix them separately. Likewise for space. Best way would be:
SELECT *
FROM persnl
WHERE NOT CHARINDEX(', ',p_supervis)>0
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

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