user3931315 user3931315 - 9 days ago 4x
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.


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){
    $return_string ='';
    if(preg_match($pattern, $string)){
            if(stripos($temp_array[0][0],'von ')===0 ||stripos($temp_array[0][0],'der ')===0){
            }else {
                $convert = mb_strtolower($temp_array[0][0]);
                $return_string .= mb_strtoupper(mb_substr($convert,0,1)).mb_substr($convert,1);

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