I have a list of names, such as: Doe John James
, Doe Jane
and Doe Henry Albert Sr
I need to split those out into the four component parts of the name (if the name has four parts) to put each of them in their own column (last name, first name, middle name, suffix), while allowing for names that may or may not have a middle name or suffix.
I've got it figured out for everything but the third instance, where there is a suffix.
For first name I have:
case
when LEN(LTRIM(rtrim(@name))) - LEN(replace(ltrim(rtrim(@name)),' ','')) = 2
then LEFT(SUBSTRING(LTRIM(RTRIM(@name)), CHARINDEX(' ', LTRIM(RTRIM(@name))) + 1, LEN(LTRIM(RTRIM(@name)))), CHARINDEX(' ', SUBSTRING(LTRIM(RTRIM(@name)), CHARINDEX(' ', LTRIM(RTRIM(@name))) + 2, LEN(LTRIM(RTRIM(@name))))))
else
SUBSTRING(@name, CHARINDEX(' ', @name) + 1, LEN(@name))
end as firstName
It's pretty crazy, but it works…
For middle name I have:
case
when LEN(LTRIM(rtrim(@name))) - LEN(replace(ltrim(rtrim(@name)),' ','')) = 2
then
REVERSE(SUBSTRING(REVERSE(LTRIM(RTRIM(@name))),1,CHARINDEX(' ',REVERSE(LTRIM(RTRIM(@name))))))
else ''
end as middleName
Not quite as complex as the first name.
Since the last name is at the beginning that's the easiest:
SUBSTRING(LTRIM(RTRIM(@name)),1,CHARINDEX(' ',LTRIM(RTRIM(@name)))-1) as lastName
But I can't figure out how to allow for names with a suffix (Jr, Sr, III, etc).
Best Answer
Does something like this help you:
n
from 1 to up to 4.n
the GROUP BY put them in their respective columns.On a big table, it may not be very efficient though.
Output:
Data: