MySQL - Capitalize first letter of each word, in existing table
I have an existing table 'people_table', with a field full_name.
Many records have the 'full_name' field populated with incorrect casing. e.g. 'fred Jones' or 'fred jones' or 'Fred jones'.
I can find these errant entries with:
SELECT * FROM people_table WHERE full_name REGEXP BINARY '^[a-z]';
How can I capitalize the first letter of each word found? e.g. 'fred jones' becomes 'Fred Jones'.
Answer
There's no MySQL function to do that, you have to write your own. In the following link there's an implementation:
http://joezack.com/index.php/2008/10/20/mysql-capitalize-function/
In order to use it, first you need to create the function in the database. You can do this, for example, using MySQL Query Browser (right-click the database name and select Create new Function).
After creating the function, you can update the values in the table with a query like this:
UPDATE users SET name = CAP_FIRST(name);