Separate full name into diff fields

Question

Separate full name into diff fields

latitiacasta

SSCommitted

Points: 1982
More actions
January 10, 2009 at 9:08 am

#94952

I have one table that have fullname column and i have another table with fname,lname,mname,prefix,suffix. I pulled data from fullname to this new table but problem is when field is missing into fullname table its give me incorrect data into new table.
for eg. Mr. Kevin R Cox
so prefix Mr.
fname Kevin
lname cox
mname R its work fine
but when something like this Kevin Cox
then its not working
Prefix Kevin
fname COx
lname null
mname null
Pls help me ASAP.
Thanks,
Laetitia

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 1

You will have to build a series of logic steps to handle this. You need to know what prefixes to look for and handle those (Mr., Mr, Ms, Ms., Mrs, etc.) and strip those out. If none of those exist, then you have no prefix.

for separating out names, the same thing applies. How do you handle "J Steven Jones" or "Kendall Van Dyke"?

This isn't a SQL issue, it's a data cleanup issue and you might need to make multiple passes to clean things up.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

In addition to Steve's advice I would recommend that you prepare to do some manual changes as well. You may be able to get it ALL right via programming, but it will likely take longer than fixing the anomalies like Kendall Van Dyke, not that Kendall is an anomaly, but his name would be.:P

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

latitiacasta SSCommitted Points: 1982 More actions · Answer 3

Thanks steve,

If possible then pls provide me code with small example.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

There are way too many variations of how a name may be entered to write some "simple" SQL for this. Instead of trying to reinvent the wheel, why don't you just buy a shrink wrapped name splitter from someone and get it right the first time? I'm thinking you should do that ASAP. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jerry Hung SSChampion Points: 12968 More actions · Answer 5

or you can write the code, and share with us, even blog about it too

Sounds like a daunting task, splitting springs with MANY variations/permutations

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 6

You could grab the prefix like this

case

when substring( namefield, 1, charindex(' ', namefield) = 'Mr'

then prefix = 'Mr'

when substring( namefield, 1, charindex(' ', namefield) = 'Mr.'

then prefix = 'Mr'

when substring( namefield, 1, charindex(' ', namefield) = 'Ms'

then prefix = 'Ms'

else prefix = ''

there are more things here. The problem is that once you do this, you then need to process separately those rows with a prefix from those without one.

I'd think about either making this a project and blogging or writing an article on this or just purchasing a component to solve this.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Steve Jones - Editor (1/12/2009)
You could grab the prefix like this
case
when substring( namefield, 1, charindex(' ', namefield) = 'Mr'
then prefix = 'Mr'
when substring( namefield, 1, charindex(' ', namefield) = 'Mr.'
then prefix = 'Mr'
when substring( namefield, 1, charindex(' ', namefield) = 'Ms'
then prefix = 'Ms'
else prefix = ''
there are more things here. The problem is that once you do this, you then need to process separately those rows with a prefix from those without one.
I'd think about either making this a project and blogging or writing an article on this or just purchasing a component to solve this.

Then, there's things like the following...

Mr and Mrs soandso

Dr and Mrs soandso

Mr & Mrs soandso

Dr & Mrs soandso

Miss soandso

Ms soandso

Mister and Misses soandso

And, then partnership names like...

Mr John E Edwards and Mr Samual E Morris

Mr John E Edwards / Mr Samual E Morris

and about a bazillion other permutations due to the multitude of prefixes, suffixes, titles, accolades, and name formats. And, that just for the U.S.A. 😛

I'm thinking that an existing shrink-wrapped product is probably the way to go for name splitters just like a shrink wrapped solution is much easier that trying to split and certify addresses.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)