How to lowercase the whole string keeping the first in uppercase in MYSQL

Question

My Table column -

enter image description here

My expected Output to change in column -

Smith, Allen, Doyle, Dennis, Baker, Waker

This is what i tried, but not working :( -

UPDATE TABLE `employee`
SET last_name = UCASE(LEFT(lower(last_name), 1))

UPDATE TABLE `employee`
SET last_name = ucase(lower(last_name),1)

Followed this link too - Resource

ERROR --

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE `employee` SET last_name = UCASE(LEFT(lower(last_name), 1))' at line 1

Let me know what I am doing wrong and how to fix.

@sfah can you please specify more that which part of the question/answer is relevant in my context as I m totally confused with the link you provided for this ?? or better add an answer :) — swapnesh, Jan 10 '13 at 05:42
@swapnesh what is the name of your table? see my answer below. — John Woo, Jan 10 '13 at 05:44
@JW. Thx its working plz find my additional comment under the answer — swapnesh, Jan 10 '13 at 05:46
@JW. although your previously added answer was also working..just for the curiosity i am asking why you edit that even though thats working too — swapnesh, Jan 10 '13 at 05:47

John Woo · Accepted Answer · 2013-01-10T05:41:10.797

10

TABLE is a reserved keyword. It should be escaped with backtick.

I think TABLE should not be in your query, (i think it is a typo)

UPDATE employee
SET last_Name = CONCAT(UCASE(LEFT(last_Name, 1)), LCASE(SUBSTRING(last_Name, 2)))

SQLFiddle Demo

edited Jan 10 '13 at 05:41

answered Jan 10 '13 at 05:35

John Woo

258,903
69
498
492

Thanks for the working solution and the fiddle, but will you please expand a lit bit about the backtick comments as I really feel uncomfortable with these when to use or not (although in this case its working with backtick inclusion and exclusion as well) – swapnesh Jan 10 '13 at 05:45
@swapnesh here are the list if [MySQL rserved Keywords List](http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html). if you use any of the following, a backtick is required to escape. – John Woo Jan 10 '13 at 05:52
very sincere thanks for the link :) – swapnesh Jan 10 '13 at 05:53

score 2 · Answer 2 · edited Jan 10 '13 at 06:02

2

try this it may work

update `employee` 
set name=concat(left(upper(last_name),1),right(lower(last_name),length(last_name)-1));

edited Jan 10 '13 at 06:02

swapnesh

26,318
22
94
126

answered Jan 10 '13 at 06:00

vidyadhar

3,118
6
22
31

score 0 · Answer 3 · answered Apr 23 '14 at 02:36

In case someone here is looking also how to upper case First Name and Last Name in say a Name column that contains both, here is a snippet that maybe useful:

 SELECT
    CONCAT( 
     UCASE(LEFT(substring_index(name, ' ',1),1)), 
     LCASE(SUBSTRING(substring_index(name, ' ',1),2)),
    ' ',
     UCASE(LEFT(substring_index(name, ' ',-1),1)), 
     LCASE(SUBSTRING(substring_index(name, ' ',-1),2))
    ) as name
FROM table

How to lowercase the whole string keeping the first in uppercase in MYSQL

3 Answers3

Linked