10
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
10
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
10
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.
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
@Gustavo Piucco, this way is simpler, you can create a routine by passing the clause "Where", in the previous answer will be slower to run
-1
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;
Browser other questions tagged mysql
You are not signed in. Login or sign up in order to post.
https://www.google.co.jp/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=ucfirst%20mysql
– Daniel Omine