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.
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