Uppercase in the first letter of all the words of a column in MySQL

Question

Uppercase in the first letter of all the words of a column in MySQL

Navigation

#1 by (8 votes)
#2 by (2 votes)
#3 by (0 votes)

9

How do I make the first letter of all words in a specific column be converted to uppercase using pure MySQL p>

Example:

gustavo silva
Guilherme souza
joão Silva
maria souza

To:

Gustavo Silva
Guilherme Souza
João Silva
Maria Souza

mysql

asked by anonymous 30.06.2015 / 06:29

3 answers

How to do a search to know if a string is inside a vector in C # What is the "application / ld + json" type used in a script tag?

score 8 · 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, do not you?

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 just enough:

SELECT CAP_FIRST('gustavo silva')

Will return

Gustavo Silva

The credits are not mine, I just came across the same problem recently and this was the solution I used.

link

Edit: I leave only one final comment. This functionality should be implemented in the frontend whenever possible. Manipulation of strings and loops are usually quite problematic for DBMSs and should be avoided whenever possible. Especially if it is a query that will be executed many times.

score 2 · Answer 2

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

30.06.2015 / 08:02

score 0 · Answer 3

The solution is great, but, find a problem in Portuguese where we use the "da", the "of", the "of" 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;