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

Bello

Answer 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 ;

Cousin

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"); – Foppish

Answer 2

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

Padlock

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

@Beriosova 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. – Superincumbent

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

Answer 3

table_name = cricket_team
column_name = player_names

select player_names, concat(
  upper(substring(substring_index(player_names,' ',1),1,1)),   
  lower(substring(substring_index(player_names,' ',1),2)) , ' ',
  upper(substring(substring_index(player_names,' ',-1),1,1)),
  lower(substring(substring_index(player_names,' ',-1),2)) 
  ) as casing_player_name
from cricket_team;

Mayst

Answer 4

The easiest one, one line answer to convert into Camel case where name is the column:

select CONCAT(UPPER(substr(name,1,1)),LOWER(substr(name,-(length(name)-1),length(name)-1))) name from Users

Embarkation