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?
gogetsomeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
update yourTable
SET Name = RIGHT(Name, len(Name) - charindex(',', Name) )+' ' +LEFT(Name, len(Name) - charindex(',', Name)-1 )
where name like '%,%'
0
ggzfabCommented:
Try a column like:

case when charindex(', ','Lawrence, Michael')>0 then  SUBSTRING ('Lawrzzence, Michael',charindex(', ','Lawrzzence, Michael')+1,999) + ' ' + left('Lawrence, Michael',charindex(', ','Lawrence, Michael')-1)  end

Open in new window



Just replace 'Lawrence, Michael' with your fieldname.
0
gogetsomeAuthor Commented:
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?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Aneesh RetnakaranDatabase AdministratorCommented:
Seems like you got a space after the comma

Update your table
set name = replace (name, ',','')
where name like '%,%'
0
gogetsomeAuthor Commented:
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?
0
Aneesh RetnakaranDatabase AdministratorCommented:
How do you distinct between a company name and a person's name  ?
0
gogetsomeAuthor Commented:
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?
0
Aneesh RetnakaranDatabase AdministratorCommented:
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 '%.%'
0
gogetsomeAuthor Commented:
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.
0
Aneesh RetnakaranDatabase AdministratorCommented:
Sorry My mistake

select Name,updatedName = RIGHT(Name, len(Name) - charindex(',', Name) )+' ' +LEFT(Name, charindex(',', Name)-1 )
from your table
where name like '%,%'
and name not like '%.%'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gogetsomeAuthor Commented:
Thank you so much for helping Aneeshattingal! That will work and seems to be my only option.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.