formating in sql 2008

Hello,
I need to change the capitals from this
Lastname         Firstname
SMITH              KEITH KIRKWOOD
PHIPPS              OWEN CHARLES
BERENDSE       ANTONIA GERRANDINA
TAYLOR              DOREEN MARGARET
MURRELL              NANCY BERYL
TUSTIN              LILY

to this
Lastname         Firstname
Smith                Keith Kirwood
Phipps              Owen Charles
Berendse          Antonia Gerrandina
Taylor                Doreen Margaret
Murrell              Nancy Beryl
Tustin                Lily

The names are consistent I have over 5000 of them, they are in the fields
Lastname and Firstname on my customer table.

I am sure there is a script to do this
Amanda WalshawBusiness Solutions AnalsystAsked:
Who is Participating?
 
Koen Van WielinkConnect With a Mentor IT ConsultantCommented:
Hi Flyfish,

This should work:

update	yourtable
set			firstname = Replace(Firstname,reverse(left(reverse(firstname),len(firstname)-1)),  reverse(lower(left(reverse(firstname),len(firstname)-1))))
		,	lastname = Replace(Lastname,reverse(left(reverse(Lastname),len(Lastname)-1)),  reverse(lower(left(reverse(Lastname),len(Lastname)-1))))

Open in new window


Best regards,

Kvwielink
0
 
Koen Van WielinkIT ConsultantCommented:
Hi Flyfish,

This should work:

update	yourtable
set			firstname = Replace(Firstname,reverse(left(reverse(firstname),len(firstname)-1)),  reverse(lower(left(reverse(firstname),len(firstname)-1))))
		,	lastname = Replace(Lastname,reverse(left(reverse(Lastname),len(Lastname)-1)),  reverse(lower(left(reverse(Lastname),len(Lastname)-1))))

Open in new window


Best regards,

Kvwielink
0
 
Koen Van WielinkIT ConsultantCommented:
Hi Flyfish,

This should work:

update	yourtable
set			firstname = Replace(Firstname,reverse(left(reverse(firstname),len(firstname)-1)),  reverse(lower(left(reverse(firstname),len(firstname)-1))))
		,	lastname = Replace(Lastname,reverse(left(reverse(Lastname),len(Lastname)-1)),  reverse(lower(left(reverse(Lastname),len(Lastname)-1))))

Open in new window


Best regards,

Kvwielink
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Koen Van WielinkIT ConsultantCommented:
Sorry about all the triple posts by the way, still haven't figured out how this happens. I'm pressing submit only once.
0
 
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
hmm anyway kvwielink it worked, I remembered after i sent the post, I need to take a break.
Thanks again.l
0
 
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
excellent and too the point
0
All Courses

From novice to tech pro — start learning today.