How best to parse full name out to FIRST, MIDDLE, LAST

[bogojugolev]

Hey,
I have a name field in one of my tables that I'd like to break out into FIRST/MIDDLE/LAST.

Does anyone have an SQL approach for parsing this kind of name field?

Thanks!

 

[bassguy]

I have had the similar problem. I also have bad news. There is no easy way to seperate names as there is no standard as to what makes up a name. For instance:

'Mary Jane Smith'

would be a no brainer parse the string at the spaces.
but what happens with the name?

'Liam Robert Mc Mahon'

We can no longer separate on the spaces. Now Mary Jane Married Liam Robert but kept her last name and added his.

'Mary Jane Smith Mc Mahon'

We are really stuck now....and I haven't even mentioned that they had a son

'Liam Robert Mc Mahon Jr.'

and a daughter

'Mary Ellen Louise Mc Mahon'

sorry, i am getting a bit carried away but as you can see it is a mess.

I finally had to parse the names column and then eyeball anything that had more than 3 names then handle them by hand. it is a mess

sorry I could not help

Bassguy

 

[RobHudson]

I agree with the previous posting it's a real nightmare!!

I have done this for you that might help you get you moving in the right direction:

SELECT FIELDNAME, SUBSTRING(FIELDNAME, 0, CHARINDEX(' ', FIELDNAME)),
SUBSTRING(FIELDNAME, CHARINDEX(' ', FIELDNAME), LEN(FIELDNAME) - CHARINDEX(' ', FIELDNAME) + 1)
FROM TABLENAME

Would it be possible for you to write a DTS that has a VBScript module in it? Loop through the data and build a table of the names?? That way, you could make the breaking up a lot more complex!

Good luck
Rob