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.
x
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”
Advertisement
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)