asked on

Correcting Capitalization in a table

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?

jayrod

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

jayrod

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)))

ASKER CERTIFIED SOLUTION

jayrod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

arbert

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

jayrod

thanks arbert.. I'll be using that UDF from now on if ya don't mind :)

jkalkowski

ASKER

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