uppercase first letter of each words (utf8) in MySQL

Question

EDIT: Adding CHARSET utf8 to input param and output make it works. i found this code snippet to convert first letter of every words to uppercase

DELIMITER $$
CREATE FUNCTION CAP_FIRST (INPUT VARCHAR(255) CHARSET utf8)

RETURNS VARCHAR(255) CHARSET utf8

DETERMINISTIC

BEGIN
    DECLARE len INT;
    DECLARE i INT;

    SET len   = CHAR_LENGTH(INPUT);
    SET INPUT = LOWER(INPUT);
    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$$
DELIMITER ;

while it works for ascii string; it doesn't work for utf8

like when i do select cap_first('tiếng việt'); ; i'm getting Ti?ng Vi?t as result.

my table is in utf8_general_ci

Answer 1

It seems like you need to use SET NAMES to tell the function what charset you're using. Try:

...
SET NAMES utf8 COLLATION utf8_general_ci;
SET len   = CHAR_LENGTH(INPUT);
...

Manual reference:

From http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

SET NAMES indicates what character set the client will use to send SQL statements to the server. Thus, SET NAMES 'cp1251' tells the server, “future incoming messages from this client are in character set cp1251.” It also specifies the character set that the server should use for sending results back to the client.

Not sure if you need to specify the collation in this case.