How can I capitialize the first letter of each word mysql column

Databases
1

Hi can I ask some help please. I have column like student_name and I want to capitalize the first letter of each word .

ex: beth anne

then it will convert to

Beth Anne

Thank you in advance

2

First, you should be storing the first and last name in their own columns. I suggest you fix the DB.

Because of the “incorrect” db design, the solution to what you want to do is more complicated than it would have been with just a single name in each column.

For a single column with one name the query would have been:

UPDATE students
SET first_name = CONCAT(UCASE(SUBSTRING(first_name, 1, 1)), LOWER(SUBSTRING(first_name, 2)));

But, for a single column with only two names, first and last, this will update your DB.

UPDATE students
SET student_name = CONCAT(
    UPPER(SUBSTRING(student_name, 1, 1)),
    LOWER(SUBSTRING(student_name, 2, LOCATE(' ', student_name) - 2)),
    ' ',
    UPPER(SUBSTRING(student_name, LOCATE(' ', student_name) + 1, 1)),
    LOWER(SUBSTRING(student_name, LOCATE(' ', student_name) + 2))
)
WHERE student_name LIKE '% %';
1 Like
4

Hi thank you,

But how can I run that in one SQL? because I have 1 million records. I will update via php script in the background.

5

What about a name like

Huber van Eiken

In that Case you will be lost anyway…

1 Like
6

Or handle it through the output. CSS’s text-transform: capitalize will also accomplish this (though as Thallius points out, is this actually what you want to do?)

1 Like
7

Hi Guys

Limit only to two words.

so if my column has 1 value only capitalized the first Letter if 2 words or more like Thallius said. Only the 2 word will be capitalized the first letter.

8

What about hyphenated words?
Should Beth ann smith be Beth Ann Smith or Beth ann Smith? Henry Wadsworth Longfellow? Francis Ford Coppola? (A computer has no concept of what that middle word is - it’s all just letters in a string.)

9

Hi

No worries about that because it is already in small caps when I inserted it into the table.

Thanks

10

a SQL table doesnt have the concept of “small caps”, because a SQL table doesnt have a typeface.

11

Hi

During entry of my data using php I convert to strtolower so all data inserted to my table column name is in lower case format.

12

right. So if you’ve rendered Francis Ford Coppola’s name, it’s now francis ford coppola. If you then capitalize only 2 words in the name, you get Francis ford Coppola.

My point is, there are some people who are known by more than 2 word names. SOME of them capitalize all the words. Some of them dont. How do you distinguish? Human brain.

13

so ‘curly’ becomes ‘Curly’

but ‘curly joe’ becomes ‘curly Joe’?

please confirm

14

you do realize OP’s problem has nothing to do with human names, right?

15

That being the case, it is pointless to then go back and update the DB data. From the comment from@r937 it seems you may have something else going on that you are not disclosing. If that is the case, you need to tell us exactly what you have going on or you are just wasting our time trying to resolve some hypothetical scenario. I withdraw my previous advice until I know what we are really dealing with.

16

Not sure if joking or misinterpreting “student” to be some sort of domesticated animal?

17

could be any 1- or 2-word thingamajig, disguised as “student name”

18

Hi

curly joe will convert to Curly Joe,

by the way, my column student name is not combined with Firstname and Lastname.

It is only the student’s first name.

ex:

anne will convert to Anne

jane marie will convert to Jane Marie.

Thank you

1 Like
19

okay great

please try the SQL provided in @benanamen’s initial reply to you

1 Like
20

Thanks for solving this, I wanted to do something similar on my site, but was having the same issue.

21

Thank you all :slight_smile: