July 12, 2006 at 6:54 am
Hi,
I have a table with various columns, three of which are called full_name, first_name & last_name.
I need some SQL script I can add to a DTS package that will point to the table and the column and then copy (from full_name column) the first name to first_name and last name to the last_name column.
Can you help?
Regards,
Brian
July 12, 2006 at 7:04 am
Brian
It depends on the format your full names are in. Is it "Lastname, Firstname" or "Firstname Lastname"? Are titles and/or middle names included in the full names? How will you resolve double-barrelled surnames?
In any case, I would advise you not to do this, since you will be breaking one of the rules of normalisation by having redundant data in your table. I would recommend creating columns called Title, FirstName, MiddleName, LastName and populating these individually. If you ever need to display the full name, you can have your client application manipulate and concatenate for you.
John
July 12, 2006 at 7:06 am
Hi Brian,
such a script would depend on how the data is stored in the full_name column. Specifically, how is the first and last name seperated - with a space, a comma, or something else?
If it's a space then there could be a problem in that some first names contain spaces in them (e.g. John Paul or Mary Anne).
If it's a comma (e.g. John Paul,Smith) then that's easy:
update mytable
set first_name = mid(full_name, 1, charindex(',',full_name)-1),
last_name = mid(full_name,charindex(',',full_name)+1))
If the character that seperates a last name from the first name is a space you can use a space instead of a comma in the charindex function. Just be aware that this will not work for first names that contain spaces in them.
Hope that helps,
p.s. Just saw John's thread and his comments about double-barelled surnames also figure into the above warning.
July 13, 2006 at 8:46 am
Hi!
Thanks for your help - I'll go try the script.
Regards,
Brian
July 13, 2006 at 10:31 am
If full_name is stored with separating spaces, as in 'Franklin Delano Roosevelt', we must assume that anything after the last space is the surname. It's more likely that to have two-part first names or middle initials than non-hyphenated double-barreled surnames. Even in the case where the last two words *appear* to be surnames, you can never be certain. Unless you know the person, you won't know whether 'Delano' is the middle name or surname.
This will put the word after the last space into last_name and everything else into first_name.
update mytable
set first_name = left(full_name, len(full_name)-Charindex(' ',reverse(full_name))),
last_name = reverse(Left(reverse(full_name), Charindex(' ',reverse(full_name))))
July 13, 2006 at 1:46 pm
I've done similar kinds of processing and it would be nice if you could always have the same format (i.e. last, first) but that never happens on a consistent basis in an unformatted field. So you need to establish a pecking order of determining what you've got.
If the charindex on a comma finds one, I think its pretty safe to assume you've got last, first. Unless of course its "Joe Smith, Esq" or something like that. So maybe you should check for common titles like DDS, MD, Jr, etc first and move them to another field- analyze your results and these will jump out at you. Once you eliminate that the "last, first" should work IF there's a comma.
If not, first eliminate multiple spaces between words (replace two with one, ltrim, rtrim). then you can count up how many spaces there are in between words. If there are more than one assume it is formatted as "first middle last". Then find the position of the last space put anything right of that in last name. Anything between the two spaces as middle and anything left of the first space as first name. Even if its a two word first or last name, when you pull first middle and last together in your app it should look like a real name.
Gianni Cache
July 18, 2006 at 10:06 am
I've got one of those "non-hyphenated double-barreled surnames". It sure is an inconveniance when I get misfiled because the database only recognized the word after the last space as my last name. The only real solution is to capture the last and first name in separate columns in the first place. We use prefix, first, middle, last, suffix.
When parsing a name from a full name field we look for the "title" (Jr Sr II III MD) before and after the comma. For example: Smith Jr., John or Smith, John Jr.
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply