SQL, how to standardize the full name?

Hi Experts,

In the raw data, some first name and last name are in upper case, like MICHAEL BROWN, some are  not, like Michael Salata.
How can I standardize them to Michael Salata?  Thank you!


Select (vps.UserFirstName) + ' ' + (vps.UserLastName) as VPName,

Open in new window

tanj1035Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Rgonzo1971Commented:
Hi,

pls try something like this

Select upper(substring(vps.UserFirstName,1,1))+lower(substring(vps.UserFirstName,2,499)) + ' ' + upper(substring(vps.UserLastName,1,1))+lower(substring(vps.UserLastName,2,499))  as VPName
Regards

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
Vitor MontalvãoMSSQL Senior EngineerCommented:
Select UPPER(LEFT(vps.UserFirstName,1)) + LOWER(RIGHT(vps.UserFirstName,LEN(vps.UserFirstName)-1)) + ' ' + UPPER(LEFT(vps.UserLastName,1)) + LOWER(RIGHT(vps.UserLastName,LEN(vps.UserLastName)-1)) as VPName

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Rgonzo1971's solution only works for names up to 499 characters. Shouldn't be an issue since I don't know nobody with that big name but just be careful if you want to use the same solution to another thing, like for example addresses.
Robin SandlinCommented:
Although this would not apply to recurring select statements, if you just want to "clean up" the data table to standardize all names to use going forward you might consider the following.

Import all names into excel (using whatever method you prefer, excel has many options.  Once in Excel, use the =Proper() function, in the column next to the original name, then do a copy and paste/value for the whole column at once, then pass the corrected case names back into your data base.

Col_A      Col_B                  Col_B (Display Value)        Col_C(actual value after copy/paste value from Col_B
SALATA   =Proper(A1)      Salata                                 Salata


Then select Col_C to export/import back to database.  If you want to try  this approach, let me know and i can help with the import export if you would like.
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.