Converting all text in a column to Camel Case in MySql

Adarsh Madrecha picture Adarsh Madrecha · Mar 25, 2017 · Viewed 12.9k times · Source

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

Answer

Barmar picture Barmar · Mar 25, 2017

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 ;