Uppercase in the first letter of every word in a column in Mysql

Question

How do I get first letter of all words in a specific column be converted to capital letter using Mysql pure?

Example:

gustavo silva
Guilherme souza
joão Silva
maria souza 

To:

Gustavo Silva
Guilherme Souza
João Silva
Maria Souza  

Answer 1

This feature does not exist in Mysql, but you can set the following function to get the result you want. I consider this to be pure Mysql, so why reinvent the wheel, no?

CREATE FUNCTION CAP_FIRST (input VARCHAR(255))

RETURNS VARCHAR(255)

DETERMINISTIC

BEGIN
    DECLARE len INT;
    DECLARE i INT;

    SET len   = CHAR_LENGTH(input);
    SET input = LOWER(input); -- Opcional mas pode ser util quando queres formatar nomes
    SET i = 0;

    WHILE (i < len) DO
        IF (MID(input,i,1) = ' ' OR i = 0) THEN
            IF (i < len) THEN
                SET input = CONCAT(
                    LEFT(input,i),
                    UPPER(MID(input,i + 1,1)),
                    RIGHT(input,len - i - 1)
                );
            END IF;
        END IF;
        SET i = i + 1;
    END WHILE;

    RETURN input;
END;

To use enough:

SELECT CAP_FIRST('gustavo silva')

Will return

Gustavo Silva

The credits aren’t mine, I just came across the same problem recently and this is the solution I used.

http://joezack.com/2008/10/20/mysql-capitalize-function/

Edit: I leave only one final comment. This functionality should be implemented on the frontend, whenever possible. String manipulation and looping are usually quite problematic for Dbms and should be avoided whenever possible. Especially if it is a query that will be executed many times.

Answer 2

This code below will solve your problem:

UPDATE tabela SET coluna =
SELECT CONCAT(UPPER(SUBSTRING(coluna FROM 1 FOR 1)), 
SUBSTRING( coluna FROM 2 FOR LENGTH(coluna))) FROM tabela 

Answer 3

The solution is great, but, finds a problem in Portuguese where we use the "da", the "de", the "do" and the "e", for example "JOSE DA SILVA", using the function would return "Jose Da Silva", instead of "Jose da Silva". To fix this just use REPLACE before the final RETURN: ....

SET input=REPLACE(input,' Da ',' da ');
SET input=REPLACE(input,' De ',' de ');
SET input=REPLACE(input,' Do ',' do ');
SET input=REPLACE(input,' E ',' e ');
RETURN input;