How to capitalize the first letter of every word with SQL

Problem

You have a column in your database that contains strings, and you need to create a SQL query that will capitalize the first letter of each word in that column.

Sample Data

Consider the following sample table named users:

We want to convert the name column values to have each word start with a capital letter, like so: "John Doe", "Jane Smith", "Alexander The Great".

MySQL Solution

SELECT id, CONCAT(UCASE(LEFT(name, 1)), LCASE(SUBSTRING(name, 2))) AS CapitalizedName
FROM (
    SELECT id, TRIM(SUBSTRING_INDEX(name, ' ', -1)) AS name
    FROM (
        SELECT id, name
        FROM users
    ) AS x
) AS y;

Explanation - MySQL

In MySQL, there is no built-in function to achieve this directly so we have to use a combination of functions.

The SUBSTRING_INDEX function is used to split the name into words. The TRIM function is used to remove any leading or trailing spaces. The UCASE function is used to convert the first letter of each word to uppercase, and LCASE is used to convert the rest of the word to lowercase. This is then concatenated together using the CONCAT function.

PostgreSQL Solution

SELECT id, INITCAP(name) AS CapitalizedName
FROM users;

Explanation - PostgreSQL

PostgreSQL provides a built-in function INITCAP which converts the first letter of each word to upper case and the rest to lower case. This makes our task much simpler in PostgreSQL compared to MySQL.

Note: Both these solutions consider 'word' as any sequence of characters separated by space. Ensure that your data is appropriately cleaned for special characters if any.