Capitalize first letter of multiple words of a column without CAP_FIRST

Question

I have a table such as the below

    Name
    firstname lastname
    FirstName Lastname
    firstName Lastname
    FirstName lastname

I am trying to capitalize each letter of the firstname and lastname to be Firstname Lastname in all the 4 cases.

Any recommendations on the query without using the CAP_FIRST function? Can I use regex?

Possible duplicate of [MySQL - Capitalize first letter of each word, in existing table](http://stackoverflow.com/questions/3278207/mysql-capitalize-first-letter-of-each-word-in-existing-table) — Schwern, Mar 17 '16 at 22:18
From the examples you provided it looks like you're wanting to also convert all of the other letters to lowercase. This may not be ideal for `Firstname McLastname`, etc. — Don't Panic, Mar 17 '16 at 22:29
Yes, You got it right...need to lowercase the rest of the alphabets — Stackoverflow User, Mar 18 '16 at 00:36
As explained in [this answer](https://stackoverflow.com/a/67399882/4227328), you can create the function to do that. — Ali A. Jalil, Jun 05 '21 at 11:10

White Feather · Accepted Answer · 2016-03-18T06:58:12.337

5

This is a query that works:

SELECT 
CONCAT(
    UPPER(SUBSTRING(Name,1,1)),
    LOWER(SUBSTRING(Name,2,Locate(' ', Name)-1)),
    UPPER(SUBSTRING(Name,Locate(' ', Name)+1,1)),
    LOWER(SUBSTRING(Name,Locate(' ', Name)+2)))
FROM NameTable;

This the result:

Name
Firstname Lastname
Firstname Lastname
Firstname Lastname
Firstname Lastname

To UPDATE column use,

UPDATE NameTable 
SET NameTable.Name = CONCAT(
    UPPER(SUBSTRING(Name,1,1)),
    LOWER(SUBSTRING(Name,2,Locate(' ', Name)-1)),
    UPPER(SUBSTRING(Name,Locate(' ', Name)+1,1)),
    LOWER(SUBSTRING(Name,Locate(' ', Name)+2)));

Regards

edited Mar 18 '16 at 06:58

answered Mar 17 '16 at 22:27

White Feather

2,615
1
14
21

1

Caveat: this will only do the first two words. "first middle last" will result in "First Middle last". – Schwern Mar 17 '16 at 23:12
Thank you..Yes, it works as the above comment specified- also I need to update the column.. – Stackoverflow User Mar 18 '16 at 00:39
It was required Firstname Lastname, not First Middle Last. To UPDATE see updated answer. It would be nice if you vote and/or accept di answer... and you practice a bit more with SQL. :) – White Feather Mar 18 '16 at 06:59
2

This actually duplicated first letter of the first word for all rows without space, do not use! – dusoft Apr 24 '17 at 21:12
Yes. It will make `WANDERER` to `WWanderer`. Do not use! – Lizesh Shakya Jun 24 '21 at 06:26

Capitalize first letter of multiple words of a column without CAP_FIRST

1 Answers1

Linked