Wednesday, 28 November 2012

MySQL Correct character case of names

Sometimes a client will pass you some data with forenames, surnames or, both with people's names all in upper-case, lower-case or, a jumbled mess.  This looks a bit unprofessional when you are doing mail shots or e-mail mailings.

The below code shows how you can select the forename field, correcting the data's case.  It sets the first character to upper-case and the rest, lower-case.

SELECT CONCAT(UPPER(LEFT(forename, 1)), (SUBSTRING(LOWER(forename), 2))) As fname
FROM table1;

Alternatively, if you want to correct the data in the database table, you would...

UPDATE table1 SET forename =  CONCAT(UPPER(LEFT(forename, 1)), (SUBSTRING(LOWER(forename), 2)));

If the table contains a lot of data then, you may wish to run the UPDATE code over night, out of hours!

No comments: