Capitalize first letter only in USA customer name

Question

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"

I am satisfied with you. I also asked the client but he wants to this. — Dhiru, Jul 11 '19 at 13:32
Defining such a regular expression for the last name will be problematic, as there is no explicit grammar defined that will cover all possibilities without including false positives. — h0r53, Jul 11 '19 at 13:33
I am very frustrated. I already took a very long time for this query. so please help me — Dhiru, Jul 11 '19 at 13:34
https://stackoverflow.com/questions/4263272/capitalize-first-letter-mysql — Med Elgarnaoui, Jul 11 '19 at 13:34
If it's what the client wants, this may require a bit of manual work. I'd start with capitalizing the first letters of the first and last names, then reviewing a set of special cases where the last name includes two capital letters and adjusting accordingly. — h0r53, Jul 11 '19 at 13:34
The SQL to do this would be pretty horrendous and quite unreadable - is there any reason you couldn't do this after receiving the query results? Is there any reason you don't perform an ETL to 'clean up' the troublesome data? — schlock, Jul 11 '19 at 13:34
Yes, Actually, We tried to server-side script and script was taking a very long time. We have big data of customer so we decided that will work on SQL. — Dhiru, Jul 11 '19 at 13:42
I wonder how Emmanuel MacRon would feel about this. And I haven't seen names spelled like FitzGerald or FitzSimmons. I don't think the requirement is well thought out. — Gordon Linoff, Jul 11 '19 at 14:02
Hey dhiru if you think my answer is valid can you mark it as the answer. Thanks — schlock, Jul 23 '19 at 14:09

schlock · Accepted Answer · 2019-07-11T13:56:17.080

3

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)

edited Jul 11 '19 at 13:56

answered Jul 11 '19 at 13:50

schlock

519
3
5
14

Very thanks for your reply. But "o'neal" title, I am not getting the right result. – Dhiru Jul 11 '19 at 14:12
Just add another 'when' clause to the select where you follow the pattern used for 'Mac' as the basis for e.g. O'Neil or McDonald – schlock Jul 11 '19 at 14:16

Capitalize first letter only in USA customer name

1 Answers1