CODEWITHSUNDEEP
mysql
user3931315
user3931315

Reputation: 47

MYSQL - Format name string with separators

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 formatted with the first letter capitalized. See the following examples:

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.

Upvotes: 0

Views: 153

Answers (1)

user3931315
user3931315

Reputation: 47

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;
}

Upvotes: 1

Related Questions