[HOW-TO] Converting case in MySQL

I had been wondering this for few days. I wanted a solution which could comvert first letter of text in some fields to uppercase and the rest to lower. I got time today and experimented a bit and found a very simple solution.

CREATE TABLE  temp_users (
fname VARCHAR( 50 ) NOT NULL
email VARCHAR( 250 ) NOT NULL
) ENGINE = MYISAM ;

Query above makes a temporary table ‘temp_users’ to store the updated data.

INSERT INTO temp_users
SELECT CONCAT( UPPER( SUBSTRING( fname, 1, 1 ) ) , LOWER( SUBSTRING( fname, 2 ) ) ) , email
FROM users

This query selects the data from the current table, converts the case and inserts into our temporary table ‘temp_users’.

The converted data has now been been stored into a temporary table. Now you can drop the table ‘users’ and rename ‘temp_users’ to ‘users’.

DROP TABLE users;
RENAME TABLE temp_users TO users ;

And that is all.

Cheers!