Does anyone have a solution to changing the case of characters in VARCHAR fields? I have several vendor names that have historically all been capitalized, and I would like to change them to Upper and Lower Case (as custom). For example:

WIDGET, INC would become Widget, Inc
ACME LTD would become Acme LTD
JOHNSON & JOHNSON would become Johnson & Johnson

I don' t mind if I have a stray character that inadvertantly gets changed to the wrong case... I can manage to the exceptions. I'm looking at roughly 11,000 records to change.

Hello,

that depends on the database that you use:

for Oracle and MySQL and MSSQL : UPDATE table SET company = UPPER(company);

If you use another database, please let us know ...

Hope that helps ?

Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

In Oracle, use the INITCAP function. However, this will convert "LTD" to "Ltd" in your example above.

UPPER would set all characters to Upper Case, would it not? (as LOWER would set all characters to Lower Case).

I'm looking for the ability to set the first characters of strings to Upper, making rest lower, similar to the INITCAP function in Oracle.

I'm guessing I could use a combination of UPPER, LOWER, and LEFT, which would get me to most of the changes, but that would leave me with lower case letters starting the second or third words in a name.

I just took a look at some SQL Server documentation, and see that there seems to be no equivalent of INITCAP. I guess you could write your own function, using UPPER, LOWER, SUBSTRING and CHARINDEX.
You would use CHARINDEX to find the spaces, then UPPER the character following each space.

Thanks. That's where I figured this was heading. That's a good direction, and I should be able to proceed from here. I appreciate the info.

I was able to get this to work (for the most part) with a combination of LEFT, RIGHT, SUBSTRING, LEN, CHARINDEX, UPPER, and LOWER commands. So it's not a bad tool for me to have on hand.

I discovered as an afternote, that if I exported my results to Excel, I could use a command Proper(field) that would automatically do this for me. I could have then brought the results back into MSSQL. Really, a much easier process, since my data sets are small enough to be handled by Excel (15,000 rows).