Capitalize first letter only in USA customer name

Need a MySQL query to update the customer first letter of the name is Capitalized and the remaining letters are lower case.

In cases where the Last name begins with the following: (Fitz, Mac, Mc, and O’) we should capitalize the following letter:

Example: McDonald, MacIntyre, O’Neal, etc.

SELECT CONCAT(UCASE(SUBSTRING(field, 1, 1)),LCASE(SUBSTRING(field, 2))) 
FROM table_name WHERE field REGEXP BINARY '^[A-Z]+$';

I expect the output of to “mcdonald, macintyre, o’neal” be “McDonald, MacIntyre, O’Neal”

Answer

To do last name transformation in MySQL you would need to do something like this.

NOTE: this is merely handling the ‘macdonald => MacDonald’ scenario.

SELECT 
   CASE
      WHEN INSTR(LCASE(Field), "mac") == 1 THEN 
         CONCAT(
           "Mac", 
           UCASE(SUBSTRING(Field,4,1)),
           LCASE(SUBSTRING(Field,5))
         )
      ELSE CONCAT(UCASE(SUBSTRING(Field,1,1)), LCASE(SUBSTRING(Field,2)))
   END AS surname
FROM table_name; 

Just add more ‘when’ statements for the other cases (or special cases)