user3931315 user3931315 - 1 month ago 15
MySQL Question

MYSQL - Format name string with seperators

I would like to format a name string in a SQL INSERT statement. The name string can contain multiple versions of whitespaces (' '), dash ('-') and dot('.'). The name string should be format with the first letter captialized. See the following examples:


  • john doe -> John Doe

  • AL ARMIN can -> Al Armin Can

  • math-LY HAROUN -> Math-Ly Haroun

  • St. john -> St. John



Finding one special character is possible, but finding a combination is the difficult part. I would appreciate any ideas.

Maybe it would make sense to check if the string is already properly formatted, but only if that would simplify things.

Answer

Thanks for all the comments. The Initcap function started me on the right path. I would like to share the code that I used, which does exactly what I need it to do. This also works with Umlaute and checks for whitespace, dot and dash.

function initCap($string){
    mb_internal_encoding('UTF-8');
    $return_string ='';
    $pattern='/[\wäöüÄÖÜßáéíóúÁÉÍÓÚ]+[\s\.\-]+/';
    if(preg_match($pattern, $string)){
        while(preg_match($pattern,$string,$temp_array,PREG_OFFSET_CAPTURE,strlen($return_string))){
            if(stripos($temp_array[0][0],'von ')===0 ||stripos($temp_array[0][0],'der ')===0){
                $return_string.=strtolower($temp_array[0][0]);
            }else {
                $convert = mb_strtolower($temp_array[0][0]);
                $return_string .= mb_strtoupper(mb_substr($convert,0,1)).mb_substr($convert,1);
            }
        }
        $return_string.=ucfirst(strtolower(substr($string,strlen($return_string))));

    } else {
        $convert = mb_strtolower($string);
        $return_string = mb_strtoupper(mb_substr($convert,0,1)).mb_substr($convert,1);
    }
    return $return_string;
}
Comments