Parsing of Full Name - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Parsing of Full Name

Author

Topic

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-06-19 : 20:11:39

Hi,
I have a name field that has fullname in it and i need to parse it to firstname, lastname, middle initial and suffix.
I used the below query to parse the Last Name and first name.

SELECT
SUBSTRING([FullName], 1, CHARINDEX(' ',[FullName]) - 1) AS LastName,
SUBSTRING([FullName], CHARINDEX(' ',[FullName]) + 1, LEN(FullName)) AS FirstName
FROM CustomerTbl

There are middel initials and suffix present in the full name. How do i parse the middle initial and suffix.

The Sample Name that appear in the table:
JONAS VICKY ==> Format without Middle Initial or suffix
MAYES MARY T ==> Format with middle initial
MCGEE, III CLIFTON ==> Format with suffix

Pls advise
Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-19 : 21:20:59

It is not an easy problem to solve, since a lot depends on human knowledge of names to do it correctly. For example, how would SQL Server know that in the name "Max von Rundstedt" that "von" is part of a two word last name, instead of the middle name?

That is why is is better to initially divide names into different columns when the table is designed.

CODO ERGO SUM

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-06-20 : 05:35:34

Yuo could do this in multiple passes....resolving the simple ones first (ie no middle initial) using scripts, and then progress to the next level of difficulty, eventually shriking the "not-converted list" down to the "very hard" ones and leaving them to be manually tuned/fixed....

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-06-20 : 12:51:10

Hi,

If i use CHARINDEX(' ',[FullName]), then the output gives 1st position where blank is present. Is there any function that i can use to find out where the 2nd position of the blank is present.

Thanks,

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-20 : 13:49:26

I've been refining and using this function for years. It is pretty robust:
http://sqlblindman.googlepages.com/formatname

e4 d5 xd5 Nf6

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-20 : 13:58:29

quote:
Originally posted by pvccaz

Hi,

If i use CHARINDEX(' ',[FullName]), then the output gives 1st position where blank is present. Is there any function that i can use to find out where the 2nd position of the blank is present.

Thanks,

Search for function fnParseString here at SQLTeam.

Peter Larsson
Helsingborg, Sweden

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-06-20 : 17:21:09

Thanks !!.. This was helpful!!.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources