Search code examples
mysqlregexreplacenamesdata-munging

MySQL add period to name initials if no period exists

I have a dataset like this:

Juan Corona Jane L Doe John Q. Public R S Fitzgerald

I need to clean this up so it's:

Juan Corona Jane L. Doe John Q. Public R. S. Fitzgerald

But since MySQL doesn't support regex search and replace I feel like I'm in a bit of a stumper.

Better to do with is in the database, but as a fallback I can do it in PREG_REPLACE, where I know it can be done.

Solution 
  • SELECT TRIM(  -- remove sourrounding spaces
    REPLACE(  -- remove remaining double spaces
    REPLACE( 
    REPLACE( 
    REPLACE( 
    -- ...  
    REPLACE( 
    REPLACE( 
    REPLACE( 
        REPLACE( CONCAT( ' ' , 'W A Mozart', ' '), ' ', '  '), 
    ' A ', ' A.'),
    ' B ', ' B.'),
    ' C ', ' C.'),
    -- ...
    ' X ', ' X.'),
    ' Y ', ' Y.'),
    ' Z ', ' Z.'),
    '  ', ' ')  -- remove remaining double spaces
);

    Ahem, that is a pragmatic solution ... Plus, you can easily specify the letters to be regarded as an abbreviation or not.

    The sourrounding space padding helps to find single letters at the beginning and the end of the string.

    The initial double space padding is required to make the functions inner workings match shared spaces between two letters.

    Remaining helper spaces are removed by the two outermost function calls of TRIM and REPLACE.

    Remember, the matching is case-sensitive.