SirRatty SirRatty - 3 months ago 27
MySQL Question

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);