How to Capitalize the First Letter of Each Word in a MySQL Table Field Without Recreating It?

Capitalizing the First Letter of Each Word in an Existing Table

Question:

An existing database table contains a field called "full_name" with inconsistent casing in many records. How can the first letter of each word in these records be capitalized without recreating the table?

Many records have the 'full_name' field populated with incorrect casing. e.g. 'fred Jones' or 'fred jones' or 'Fred jones'.

Solution:

MySQL doesn't offer a built-in function to capitalize the first letter of each word. However, a custom function can be defined to accomplish this task:

Create a Custom Function:

Follow the steps below or refer to the link provided to create the custom function:

1. Create a new function in the database using MySQL Query Browser or a similar tool.
2. Copy and paste the code from the provided link: http://joezack.com/index.php/2008/10/20/mysql-capitalize-function/
3. Save the function in the database.

Update Table Records:

Once the function is created, update the 'full_name' field values using the following query:

UPDATE people_table SET full_name = CAP_FIRST(full_name);

The CAP_FIRST function will capitalize the first letter of each word in the 'full_name' field, correcting the casing inconsistencies.

The above is the detailed content of How to Capitalize the First Letter of Each Word in a MySQL Table Field Without Recreating It?. For more information, please follow other related articles on the PHP Chinese website!