Solved

formating in sql 2008

Posted on 2013-05-22
6
192 Views
Last Modified: 2013-05-22
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
0
Comment
Question by:Amanda Walshaw
  • 4
  • 2
6 Comments
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 500 total points
Comment Utility
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Sorry about all the triple posts by the way, still haven't figured out how this happens. I'm pressing submit only once.
0
 

Author Comment

by:Amanda Walshaw
Comment Utility
hmm anyway kvwielink it worked, I remembered after i sent the post, I need to take a break.
Thanks again.l
0
 

Author Closing Comment

by:Amanda Walshaw
Comment Utility
excellent and too the point
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now