Update author names from last first to first last
Hello, I have a mixed column of names similar to this:
Lawrence, Michael
Getty, Keith
Getty, Kristyn
NULL
NULL
Mainetti Stefano
Various
Nelson Bibles
I need to perform an update statement that will correct the author name if it is last,first.
So if the column contains a comma, put the word after the comma first then a space then the word before the comma and finely remove the comma.
How can this be accomplished?
Lawrence, Michael
Getty, Keith
Getty, Kristyn
NULL
NULL
Mainetti Stefano
Various
Nelson Bibles
I need to perform an update statement that will correct the author name if it is last,first.
So if the column contains a comma, put the word after the comma first then a space then the word before the comma and finely remove the comma.
How can this be accomplished?
Try a column like:
Just replace 'Lawrence, Michael' with your fieldname.
case when charindex(', ','Lawrence, Michael')>0 then SUBSTRING ('Lawrzzence, Michael',charindex(', ','Lawrzzence, Michael')+1,999) + ' ' + left('Lawrence, Michael',charindex(', ','Lawrence, Michael')-1) endJust replace 'Lawrence, Michael' with your fieldname.
ASKER
Thank you aneeshattingal!
That almost did it but left the comma at the end of the name
I suppose we could run that statement, but how to get rid of the comma at the end of the name if exists?
That almost did it but left the comma at the end of the name
I suppose we could run that statement, but how to get rid of the comma at the end of the name if exists?
Seems like you got a space after the comma
Update your table
set name = replace (name, ',','')
where name like '%,%'
Update your table
set name = replace (name, ',','')
where name like '%,%'
ASKER
Aneeshattingal,
another issue for me is that the column also contains company names like
Antiquities Research Films, Inc.
became
Inc. Anti
any way around this as well?
another issue for me is that the column also contains company names like
Antiquities Research Films, Inc.
became
Inc. Anti
any way around this as well?
How do you distinct between a company name and a person's name ?
ASKER
I inherited a mess.
The only pattern I can see is single word coma single word and not update to rows that don't fall into that pattern.
Is that possible?
The only pattern I can see is single word coma single word and not update to rows that don't fall into that pattern.
Is that possible?
You can update the where condition.
select Name,updatedName = RIGHT(Name, len(Name) - charindex(',', Name) )+' ' +LEFT(Name, len(Name) - charindex(',', Name)-1 )
from your table
where name like '%,%'
and name not like '%.%'
select Name,updatedName = RIGHT(Name, len(Name) - charindex(',', Name) )+' ' +LEFT(Name, len(Name) - charindex(',', Name)-1 )
from your table
where name like '%,%'
and name not like '%.%'
ASKER
On the surface that does look better but look what it is doing
Morecraft, Joe Joe Mor
Can't have it truncating parts of an authors name.
Morecraft, Joe Joe Mor
Can't have it truncating parts of an authors name.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much for helping Aneeshattingal! That will work and seems to be my only option.
SET Name = RIGHT(Name, len(Name) - charindex(',', Name) )+' ' +LEFT(Name, len(Name) - charindex(',', Name)-1 )
where name like '%,%'