split name to title first middle last name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

split name to title first middle last name

Author

Topic

sarora05
Starting Member

5 Posts

Posted - 2009-09-23 : 17:37:26

Hi, I am trying to split name to title first middle last name.

And I got it this far with this query:
select distinct BOOKINGCLIENTCONTACT,
PARSENAME(REPLACE(REPLACE(BOOKINGCLIENTCONTACT, '.', ''), ' ', '.'), 3) AS Title,
PARSENAME(REPLACE(REPLACE(BOOKINGCLIENTCONTACT, '.', ''), ' ', '.'), 2) AS FirstName,
PARSENAME(REPLACE(REPLACE(BOOKINGCLIENTCONTACT, '.', ''), ' ', '.'), 1) AS LastName,
PARSENAME(REPLACE(REPLACE(BOOKINGCLIENTCONTACT, '.', ''), ' ', '.'), 4) AS Last_Name
--contactfirstname,contactlastname--,c.contactid, b.clientid
from booking b

This works fine when I have 3 separators like Title First Last Name. But If I have Title First Middle and Last name then it mixes up. For example:

Name: Mr. Michael St. Jovite

It gives me
Title First Middle Last
Michel St. Jovite Mr.

How can i make it to work for three and four seprators? Please help. Thank you very much.

sarora05
Starting Member

5 Posts

Posted - 2009-09-23 : 18:26:57

hi Anyone there? Please help

Udayantha
Starting Member

4 Posts

Posted - 2009-09-24 : 00:37:35

Tell what is your in put string and what is your expected output with examples.

Bst Rgds,
Udayantha
http://sqlshit.blogspot.com/

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-24 : 01:03:49

select
BOOKINGCLIENTCONTACT,
CASE LEN(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)))-LEN(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)),' ','')) WHEN 2 THEN PARSENAME(REPLACE(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)), '.', ''), ' ', '.'), 3) WHEN 3 THEN PARSENAME(REPLACE(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)), '.', ''), ' ', '.'), 4)END AS Title,
CASE LEN(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)))-LEN(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)),' ','')) WHEN 2 THEN PARSENAME(REPLACE(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)), '.', ''), ' ', '.'), 2) WHEN 3 THEN PARSENAME(REPLACE(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)), '.', ''), ' ', '.'), 3)END AS FirstName,
CASE LEN(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)))-LEN(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)),' ','')) WHEN 2 THEN '' WHEN 3 THEN PARSENAME(REPLACE(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)), '.', ''), ' ', '.'), 2)END AS MiddleName,
PARSENAME(REPLACE(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)), '.', ''), ' ', '.'), 1) AS LastName
from #booking b

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2009-09-24 : 07:29:01

I am guessing - but 'St.Jovite' might be the last name and should not be split? Also might there sometimes be more than one middle name?

matty
Posting Yak Master

161 Posts

Posted - 2009-09-24 : 07:47:14

[code]
select distinct BOOKINGCLIENTCONTACT,
PARSENAME(REPLACE(REPLACE(BOOKINGCLIENTCONTACT, '.', ''), ' ', '.'), 4) AS Title,
PARSENAME(REPLACE(REPLACE(BOOKINGCLIENTCONTACT, '.', ''), ' ', '.'), 3) AS FirstName,
PARSENAME(REPLACE(REPLACE(BOOKINGCLIENTCONTACT, '.', ''), ' ', '.'), 2) AS MiddleName,
PARSENAME(REPLACE(REPLACE(BOOKINGCLIENTCONTACT, '.', ''), ' ', '.'), 1) AS LastName
from booking b
[/code]

sarora05
Starting Member

5 Posts

Posted - 2009-09-24 : 12:30:20

Thank you so much for your answers.
When I use this query - I noticed if just in case I have a name like this: Mr. Michael St. Jovite I cant get the first middle last name because of the "." Period. And I lose the period in the title too. Is there any way to not lose "." SO I get Mr. Michael St. Jovite
also.

Thanks again!! :)

select distinct
BOOKINGCLIENTCONTACT,
CASE LEN(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)))-LEN(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)),' ','')) WHEN 2 THEN PARSENAME(REPLACE(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)), '.', ''), ' ', '.'), 3) WHEN 3 THEN PARSENAME(REPLACE(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)), '.', ''), ' ', '.'), 4)END AS Title,
CASE LEN(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)))-LEN(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)),' ','')) WHEN 2 THEN PARSENAME(REPLACE(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)), '.', ''), ' ', '.'), 2) WHEN 3 THEN PARSENAME(REPLACE(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)), '.', ''), ' ', '.'), 3)END AS FirstName,
CASE LEN(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)))-LEN(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)),' ','')) WHEN 2 THEN '' WHEN 3 THEN PARSENAME(REPLACE(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)), '.', ''), ' ', '.'), 2)END AS MiddleName,
PARSENAME(REPLACE(REPLACE(RTRIM(LTRIM(BOOKINGCLIENTCONTACT)), '.', ''), ' ', '.'), 1) AS LastName
from Booking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources