Solved

Correcting Capitalization in a table

Posted on 2004-04-09
6
340 Views
Last Modified: 2012-08-13
I have a name database that has gotten a bit sloppy over time.  Some name enteries are all upper case, some are all lower case and others are correct.  My table is called master_name.  I am looking for a command to make the first character upper case and the rest lower.

update.dba.master_name
SET last_name??????????

How can I do this?
0
Comment
Question by:jkalkowski
  • 4
6 Comments
 
LVL 3

Expert Comment

by:jayrod
ID: 10792674

update employee_master
set fname = char(ASCII(substring(fname, 1,1)) + 32) + substring(fname, 2, len(fname))

be extremely careful with this update statement... test everywhere first
0
 
LVL 3

Expert Comment

by:jayrod
ID: 10792687
whoops.. here is an easier one... that makes more sense


update employee_master
set fname = upper((substring(fname, 1,1))) + lower(substring(fname, 2, len(fname)))
0
 
LVL 3

Accepted Solution

by:
jayrod earned 50 total points
ID: 10792705
--Correction: took out extra parenthesis
update Table
set [column] = upper(substring([column], 1,1)) + lower(substring([column], 2, len([column])))
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 34

Expert Comment

by:arbert
ID: 10792741
Here's a UDF that we use to do the job:

http://vyaskn.tripod.com/code/propercase.txt


Just remember, any weird names like McHenry will usually be a problem....

Brett
0
 
LVL 3

Expert Comment

by:jayrod
ID: 10792799
thanks arbert.. I'll be using that UDF from now on if ya don't mind :)
0
 
LVL 1

Author Comment

by:jkalkowski
ID: 10792881
perfect jayrod....U just cut and pasted, changed the [column] to read lname and it worked great.  Also Brett, good point about those pesky Irish names :-)  Actually I found a few D'Angelo's that it will effect also but I'd rather have the majority look neat!

Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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 and will be exposed to the many uses the SELECT statement has.

708 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

13 Experts available now in Live!

Get 1:1 Help Now