Parse
-
John E. Doe
Jan Smith
Ralph Wallace
Walter C Johnson
These are names in a table under a column call Contact and I would like to know what is the best way return only the first name Or last name??
-
Read BOL on CHARINDEX(), SUBSTRING() and REVERSE().
You're going to be CHARINDEX()'ing on space characters. For LastName, you're going to be REVERSE()'ing the string, finding the 1st space, stripping the leading non-space, then REVERSE()'ing the result back.
-
Create Table Contacts( name varchar(200))
go
insert into Contacts(Name) Values('John E. Doe')
insert into Contacts(Name) Values('Jan Smith')
insert into Contacts(Name) Values('Ralph Wallace')
insert into Contacts(Name) Values('Walter C Johnson')
go
select Left(Name, Len(Name) - Charindex(' ',Reverse(Name),1)-1) As FName
, Right(Name,Charindex(' ',Reverse(Name),1)-1) As LName
from Contacts
* Noel -
It's a bad table design to put 2 values (first and last name) in a single field. In my part of the country there are many 2 word last names and this algorithm will not return the last name correctly. Also will not return the first name correctly in the case of 2 word last name or middle initial. For example: Raymond St. James gets First = Raymond St., Last = James. That said, I don't know a better parsing algorithm when confronted with that table design.
Terri
TerriTo speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.] -
Noel,
while your query works on the sample data presented, it fails in two ways when you have someone like the one in bold.
It cuts off the last letter of van and does not assign van correctly as last name van Halen.
Create Table Contacts( name varchar(200))
go
insert into Contacts(Name) Values('John E. Doe')
insert into Contacts(Name) Values('Jan Smith')
insert into Contacts(Name) Values('Ralph Wallace')
insert into Contacts(Name) Values('Walter C Johnson')
insert into Contacts(Name) Values('Eddie van Halen')
go
select Left(Name, Len(Name) - Charindex(' ',Reverse(Name),1)-1) As FName
, Right(Name,Charindex(' ',Reverse(Name),1)-1) As LName
from Contacts
drop table contacts
If this is a one-time data scrubbing action, I would rather do it with a scripting language than with SQL Server. If you need to do this regularly, consider a redesign of that table.
Maybe this http://www.sommarskog.se/arrays-in-sql.html will give you some additional ideas how to handle it.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
My Post was meant as an example of what to do or at least how to accoplish it.
NO MATTER what rule you follow there are going to be always execptions. Ex: I come from Hispanic descendents and some of our names use 1-4 Names (Words) and 2-4 Surnames (words).
Of course you have to establish your rules and of course you should have designed the Database properly in the first place. The Poster didn't specified neither and the data is what it is.
My question to you (Frank and Terry) : Can you com up with something general enough that will account for all the possibilities in all parts of the world?
Cheers!
* Noel -
Can you com up with something general enough that will account for all the possibilities in all parts of the world
Noel, you know that this is almost impossible and my post wasn't intended to offend you by no means.
Such data violate 1NF and this is the reason why almost every solution ceteris paribus is a kludge. Without exactly knowing what does the data really look like, I would probably do something like this:
select
parsename(replace([name],' ','.'),3)
, parsename(replace([name],' ','.'),2)
, parsename(replace([name],' ','.'),1)
, parsename(replace([name],' ','.'),4)
from Contacts
to get the data into different columns. Then I would fetch a trainee and let him do the donkeywork to look over the data and correct it.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
By no means I was offended, and may be my words came out a little bit too strong for what I was trying to get accross (non-native speaker weakness). I appologize for that.
I am an active member of this forum and value everyones' opinion.
Cheers
* Noel -
You are a non-native speaker, too???
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply