Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

PARSE FULL NAME INTO LASTNAME,FIRSTNAME, MI Expand / Collapse
Author
Message
Posted Wednesday, January 13, 2010 10:50 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, November 6, 2014 12:03 PM
Points: 701, Visits: 450
I have name data to import into a table. This data is in the following
format:

DOE/JOHN F
JONES/MARY ALICE
SMITH/HAROLD

I need to parse this data into lastname, firstname, and middle
initial. Is there any TSQL I can quickly employ?



Post #847076
Posted Wednesday, January 13, 2010 11:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
You can split on the slash pretty easily, but the rest is trickier. For example, will you have any names that don't have a middle name? How about ones with multiple "middle" names ("Billy Bob Sam Jones")? How about splitting names with titles in them, like Dr or Rev? Or can you be certain all the names will be in the same format?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #847081
Posted Wednesday, January 13, 2010 11:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
it takes a bit of manipulating, but you need to use substrings and charindex to find the slash, and grab the left and right parts of it; you have to do the same logic a second time on the "right" side of the string, and look for the space.

note my example where i added 'RUCKUS/BILLY JOE JIM BOB' what is his first name vs his middle name? you need to look at your data and see if there are any names with more than two spaces.
here's an example:

results:
LNAME                    FirstAndMiddle           MNAME                    LNAME
------------------------ ------------------------ ------------------------ ------------------------
DOE JOHN F F JOHN
JONES MARY ALICE ALICE MARY
SMITH HAROLD HAROLD
RUCKUS BILLY JOE JIM BOB JOE JIM BOB BILLY


and here's the code:
SELECT 'DOE/JOHN F' AS FULLNAME INTO #TMP UNION ALL
SELECT 'JONES/MARY ALICE' UNION ALL
SELECT 'SMITH/HAROLD' UNION ALL
SELECT 'RUCKUS/BILLY JOE JIM BOB'


SELECT
--assumes the slash is ALWAYS in the data:
SUBSTRING(FULLNAME,1,CHARINDEX('/',FULLNAME) -1) AS LNAME, --minus one to remove the slash
SUBSTRING(FULLNAME,CHARINDEX('/',FULLNAME)+ 1,30) AS FirstAndMiddle, --add one to remove the slash
CASE
--if there is a space, ASSUME the second word is a middle name, and not a two part first name
WHEN CHARINDEX(' ',SUBSTRING(FULLNAME,CHARINDEX('/',FULLNAME)+ 1,30)) > 1
THEN SUBSTRING(SUBSTRING(FULLNAME,CHARINDEX('/',FULLNAME)+ 1,30),CHARINDEX(' ',SUBSTRING(FULLNAME,CHARINDEX('/',FULLNAME)+ 1,30)),30)
--else no middle name
ELSE ''
END AS MNAME,
CASE
--if there is a space, ASSUME the second word is a first name,
WHEN CHARINDEX(' ',SUBSTRING(FULLNAME,CHARINDEX('/',FULLNAME)+ 1,30)) > 1
THEN SUBSTRING(FULLNAME,CHARINDEX('/',FULLNAME)+ 1,(CHARINDEX(' ',FULLNAME)-1) - CHARINDEX('/',FULLNAME)+ 1)
--else whole thing is first name
ELSE SUBSTRING(FULLNAME,CHARINDEX('/',FULLNAME)+ 1,30)
END AS LNAME
from #TMP




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #847087
Posted Wednesday, January 13, 2010 11:53 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, November 6, 2014 12:03 PM
Points: 701, Visits: 450
Thank you for your quick and thorough replies. When I ran
the 2nd post SQL, I received the following message

Invalid length parameter passed to the SUBSTRING function.




Post #847111
Posted Wednesday, January 13, 2010 12:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
that would occur if any space existed in the last name, before the slash.
a name like Mac Bride/Bob Lee would make that happen... you'll need to tweak it based on your data.
from your example, i assumed the only space was between first and middle names.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #847130
Posted Wednesday, January 13, 2010 12:40 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, November 6, 2014 12:03 PM
Points: 701, Visits: 450
Thank you. I'll try and tweek the SQL you prepared based on that assumption.


Post #847132
Posted Wednesday, January 13, 2010 12:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:39 AM
Points: 6,847, Visits: 13,377
Lowell (1/13/2010)
that would occur if any space existed in the last name, before the slash.
a name like Mac Bride/Bob Lee would make that happen... you'll need to tweak it based on your data.
from your example, i assumed the only space was between first and middle names.

The lack of a slash would cause that error as well...
Seems like it's going to be quite a bit of work to get all the special patterns covered...

But that's the price one has to pay when dealing with (over-)denormalized data, I guess...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #847145
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse