Convert CamelCase to camel_case in MySQL

Question

Is there a way to convert CamelCase to camel_case using only mysql? I know I can do it in php but I wanna be able to do it in mysql, because I need to convert millions of strings.

Answer 1

Here's a solution:

delimiter $$

drop function if exists replaceCamelCase $$
create function replaceCamelCase( p_str TEXT ) returns text
BEGIN
declare v_pos int;
declare v_len int;
declare cnt int;
declare tmp text;
declare ret text;

set v_pos=1;
set v_len=1+char_length( p_str );
set ret = '';
set cnt = 0;

if p_str REGEXP('[^_]') then
while (v_pos<v_len)
do
    set tmp = SUBSTR(p_str, v_pos, 1);

    if tmp REGEXP BINARY '[A-Z]' = 1 then

        if cnt > 0 then
            set ret = concat(ret, '_');
        end if;

        set ret = concat(ret, lower(tmp));
        set cnt = cnt + 1;
    else
        set ret = concat(ret, tmp);
    end if;

    set v_pos = v_pos + 1;

end while;

else 
set ret = p_str;
end if;

RETURN ret;


end $$

User it like this:

SELECT replaceCamelCase(name) FROM `test` WHERE 1

Example input/output:

BlaTest
test_test
BaldieBal
TestStringCase

Output:

bla_test
test_test
baldie_bal
test_string_case