Split name into first name last name and middle name(If exist)

#1

Hi,

I have a column with the first name and last name of a student....and in some cases a middle name.

I am using below query to split the Name.. Its working good but except one condition where Name is like "Anna S.Biermann" .. if middle name has "."(Dot) in it my below query giving wrong results .. In rest all cases query working fine..

In my table we have Name possibilities like below.. Only in last condition where Middle name has Dot in it my query fails.. rest all cases working good..

Barbara Flowers
Allison L Wallace
Anne Renee O'Hara
Anna S.Biermann

Can you please help me in this ?

select **
** ( case LEN(REPLACE(name,' ','')) when LEN(name) - 1 then PARSENAME(REPLACE(name,' ','.'), 2) else PARSENAME(REPLACE(name,' ','.'), 3) end ) as FirstName,
** ( case LEN(REPLACE(name,' ','')) when LEN(name) - 1 then null else PARSENAME(REPLACE(name,' ','.'), 2) end ) as MiddleName,**
** PARSENAME(REPLACE(name,' ','.'), 1) as LastName**

Thank You

#2

What about a name like "Jan van Dyke"? The surname is "van Dyke" Or how about long Spanish names like that of Pablo Picasso: "Pablo Diego José Francisco de Paula Juan Nepomuceno María de los Remedios Cipriano de la Santísima Trinidad Ruiz y Picasso"

Basically there's no magic formula to catch everything. You do the best you can do and refine as you go along. Some shops use external services to validate and parse free-format names (and addresses)

#3

Ok thank you.. I did work on it.. Thanks...