[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!

Leave a Reply

Your email address will not be published. Required fields are marked *