...

View Full Version : SQL statement - changing case of a letter automatically



fjkayakr11
09-22-2006, 07:46 AM
I am running MySQL on Windows and I have a simple database setup with a
list of items (contained in the same column), to keep it simple - let's say it is a list of colors.

red
white
blue

I want to be able to run a sql statement to change only the first letter in each word to uppercase so it will read:

Red
White
Blue

I think there is a command (but I am not familiar with it) that is suppose to change the entire case of the database table columns
for example with :

UPDATE Products SET ProductName = UPPER(ProductName)
* A WHERE clause could also be used if only certain rows needed to be changed.

But I only want to change the first letter as mentioned - does anyone know how to go about this ? Thanks for any help you can give.

raf
09-22-2006, 08:27 AM
Welcome here!

try:
UPDATE Products SET ProductName = CONCAT(UPPER(SUBSTRING(ProductName,1,1)), LOWER(SUBSTRING(ProductName FROM 2)))

SQL_F1
09-27-2006, 12:08 AM
Raf is perfectly correct.
I'd like to take it one step further, however, and encourage the use of
MySQL Functions (ver 5.x).
In a one-off UPDATE, its not worth it, but if one needs a conversion in a select and on a repeated basis, try the following

CREATE FUNCTION ProperCase(strWord VARCHAR(126))
RETURNS VARCHAR(126)
DETERMINISTIC
RETURN REPLACE(strWord, left(strWord,1), upper(left(strWord,1)));


SELECT ProperCase("red");

OR

UPDATE Products SET ProductName = ProperCase(ProductName);

Since PHP merely passes query text through, the function is available there as well.

With the function in the database, its available to all apps and queries that access the database, making calculations. etc consistent across apps.

Brian
SQL_F1

guelphdad
09-27-2006, 05:36 AM
Welcome here!

try:
UPDATE Products SET ProductName = CONCAT(UPPER(SUBSTRING(ProductName,1,1)), LOWER(SUBSTRING(ProductName FROM 2)))

this will change all of the items in a field ProductName. To simply change only some of the values you can modify it like this:



UPDATE Products SET ProductName =
CONCAT(UPPER(SUBSTRING(ProductName,1,1)),
LOWER(SUBSTRING(ProductName FROM 2)))
where productname in ('red','white','blue')


any of the other values would be left untouched.

guelphdad
09-27-2006, 05:45 AM
Your function looks useful Brian but I think there is something incorrect about it.
If I run it on this sentence:


peter piper picked a peck of pickled peppers every letter 'p' gets converted to upper case. I would have thought only the first letter in the entire string would be converted?

GJay
09-27-2006, 07:59 AM
it will indeed capitalise all occurences of the first letter, probably not what you want...

SQL_F1
09-28-2006, 06:41 AM
Absolutely!
Silly and no excuses - thanks for pointing that out rather than simply following the piper.

Using Raf's correct code:

CREATE FUNCTION ProperCase(strWord VARCHAR(126))
RETURNS VARCHAR(126)
DETERMINISTIC
RETURN CONCAT(UPPER(SUBSTRING(strWord,1,1)), LOWER(SUBSTRING(strWord, 2)));

Brian



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum