Converting all text in a column to Camel Case in MySql

Question

I was searching for a way to convert all the text which I have in a database to Camel Case / Proper Case

i.e. from CAMEL HAS LEGS to Camel Has Legs

I found an answer here, which asks to create a function (below) and then use the function to convert the text.

I am using MySQL Version: 5.6.32-78.1 on a shared hosting server. When I execute the below function, I get error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 

How do I rectify this error? I am very new to MySQL

CREATE FUNCTION `proper_case`(str varchar(128)) RETURNS varchar(128)
BEGIN
DECLARE n, pos INT DEFAULT 1;
DECLARE sub, proper VARCHAR(128) DEFAULT '';

if length(trim(str)) > 0 then
    WHILE pos > 0 DO
        set pos = locate(' ',trim(str),n);
        if pos = 0 then
            set sub = lower(trim(substr(trim(str),n)));
        else
            set sub = lower(trim(substr(trim(str),n,pos-n)));
        end if;

        set proper = concat_ws(' ', proper, concat(upper(left(sub,1)),substr(sub,2)));
        set n = pos + 1;
    END WHILE;
end if;

RETURN trim(proper);
END

Solution 1

You need to use the DELIMITER statement to change the query delimiter. Otherwise, the ; inside the body ends the CREATE FUNCTION statement.

See Delimiters in MySQL

DELIMITER $$

CREATE FUNCTION `proper_case`(str varchar(128)) RETURNS varchar(128)
BEGIN
DECLARE n, pos INT DEFAULT 1;
DECLARE sub, proper VARCHAR(128) DEFAULT '';

if length(trim(str)) > 0 then
    WHILE pos > 0 DO
        set pos = locate(' ',trim(str),n);
        if pos = 0 then
            set sub = lower(trim(substr(trim(str),n)));
        else
            set sub = lower(trim(substr(trim(str),n,pos-n)));
        end if;

        set proper = concat_ws(' ', proper, concat(upper(left(sub,1)),substr(sub,2)));
        set n = pos + 1;
    END WHILE;
end if;

RETURN trim(proper);
END 
$$

DELIMITER ;

Solution 2

concat ( upper(substring(name,1,1)), lower(right(name,length(name)-1)))

Comments

[L_J]

While this code may answer the question, providing information on how and why it solves the problem improves its long-term value

[Clain Dsilva]

@L_J The answer seems straight forward and not so complicated to give a description, get the first letter make it capital, get the remaining letters and make it small. But this however does not make the whole words in a column camel case in case if there are more than one word.

[clfaster]

After you define the function you can call it like this: UPDATE table_name SET "col_name"= proper_case("col_name") WHERE id = 22; or for all elements UPDATE table_name SET "col_name"= proper_case("col_name");

[Rohit]

It just capitalizes the first letter of first word. Not an answer to the question, since it asks for CamelCase.