mysqlcamelcasingsnakecasing

Convert CamelCase to camel_case in MySQL


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.


Solution

  • 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