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

6

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,733
1
15
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

score 0 · Answer 2 · answered Mar 27 '23 at 07:40

This is a fix of White Feather answer,

The fix to avoid duplication of first character is to add IF condition in this part

UPPER(SUBSTRING(Name,1,1))

The original code is like this

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;

Then updated version will be like this

SELECT 
CONCAT(
    IF(INSTR(t1.firstname, ' ') > 0, 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;

But this only supports two words, if you have three or more it cannot be capitalized.

Capitalize first letter of multiple words of a column without CAP_FIRST

2 Answers2

Linked