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

score 11 · Accepted Answer · edited Oct 16 '17 at 08:45

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 ;

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");` — clfaster, Mar 10 '20 at 12:31

score 9 · Answer 2 · edited Jul 07 '18 at 21:29

9

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

edited Jul 07 '18 at 21:29

Zoe

27,060
21
118
148

answered Jul 07 '18 at 21:25

Dennis Leeuwin

99
1
1

8

While this code may answer the question, providing information on how and why it solves the problem improves its long-term value – L_J Jul 07 '18 at 22:05
3

@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. – Clain Dsilva Dec 23 '19 at 04:45
3

It just capitalizes the first letter of first word. Not an answer to the question, since it asks for CamelCase. – Rohit Lal Aug 01 '20 at 05:09

score 0 · Answer 3 · edited Aug 28 '21 at 09:23

0

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;

edited Aug 28 '21 at 09:23

Bill Tür stands with Ukraine

3,425
30
38
48

answered Aug 28 '21 at 08:59

blackhole

214
1
11

score 0 · Answer 4 · edited May 10 '23 at 08:59

0

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

edited May 10 '23 at 08:59

Adarsh Madrecha

6,364
11
69
117

answered May 10 '23 at 06:35

Gaurav Kumar

1

Converting all text in a column to Camel Case in MySql

4 Answers4