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

Parse fullname field into FirstName and LastName

SQL Server 2012

I've inherited a database which has several tables which contain a FullName field, but no FirstName or LastName field.

I know this is problematic, and at best will be a 95% solution, but I need a SQL query which will split the FullName column which looks like:

James Lewis
Parker, Linda
Jones-Taylor, Brenda

into

LastName        FirstName
Lewis           James
Parker          Linda
Jones-Taylor    Brenda

Open in new window


I know this is going to involve  maybe a couple of Case statements, but I'm uncertain how to parse for the text to the right or left of the comma/first space.  I know it will look something like:

SELECT FullName,
CASE WHEN [FullName] LIKE '%,%' everything left of comma
          ELSE everything to the right of the first space END as LastName,
CASE WHEN [FullName] Like '%,%' everything right of the comma
          ELSE everything to the left of the first space END as FirstName
0
Dale Fye
Asked:
Dale Fye
  • 3
  • 3
1 Solution
 
Shaun KlineLead Software EngineerCommented:
This code should get you most of the way there. It will not handle middle names or suffixes, though.

DECLARE @Test TABLE
(
	FullName VARCHAR(50)
)

INSERT INTO @Test ( FullName )
VALUES ('James Lewis'), ('Parker, Linda'), ('Jones-Taylor, Brenda'), ('John Q Smith')

SELECT FullName, 
	CASE WHEN FullName LIKE '%,%' THEN LEFT(FullName, CHARINDEX(',', FullName) - 1) 
		ELSE SUBSTRING(FullName, LEN(FullName) - CHARINDEX(' ', REVERSE(FullName)) + 2, CHARINDEX(' ', REVERSE(FullName))) END LastName,
	CASE WHEN FullName LIKE '%,%' THEN SUBSTRING(FullName, CHARINDEX(',', FullName) + 2, LEN(FullName) - CHARINDEX(',', FullName)) 
		ELSE LEFT(FullName, LEN(FullName) - CHARINDEX(' ', REVERSE(FullName))) END FirstName
FROM @Test

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What about this?
SELECT FullName,
	LEFT(FullName, CHARINDEX(',',FullName)-1) AS LastName,
	RIGHT(FullName, LEN(FullName) - CHARINDEX(',', FullName)) AS FirstName

Open in new window

0
 
Dale FyeAuthor Commented:
Thanks, Shaun.

CharIndex, SubString, and Reverse were the key words I needed.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
CharIndex, SubString, and Reverse were the key words I needed.
I'm not sure if you really need all of those functions.
Have you even tried my suggestion?
0
 
Dale FyeAuthor Commented:
Yes,

and had to modify it to account for a couple of anomolies.  Still have a few, but that has to be expected with a free text data entry and no fields for prefix or suffix in the table.
CASE WHEN FullName LIKE '%,%' THEN SUBSTRING(FullName, CHARINDEX(',', FullName) + 2, LEN(FullName) - CHARINDEX(',', FullName)) 
		ELSE LEFT(FullName, CHARINDEX(' ', FullName)) END FirstName, 
CASE WHEN FullName LIKE '%,%' THEN LEFT(FullName, CHARINDEX(',', FullName) - 1) 
		ELSE SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName) - CHARINDEX(' ', FullName))  END LastName, 

Open in new window


I had to remove the Reverse commands because there were a number of instances in the data where it included a suffix, so all I was getting was the suffix, not everything to the right of the first space.
0
 
Dale FyeAuthor Commented:
Vitor,

Thanks for your comment, but you failed to take into account that some of the records contain a comma, so they represent (Last, First) and others don't have the comma, so those represent (First Last).

Dale
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I see. I only had the comma situations.
Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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