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
You need to use the DELIMITER
statement to change the query delimiter. Otherwise, the ;
inside the body ends the CREATE FUNCTION
statement.
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 ;