Syntax error MySQL query to update all records to capitalize only the first letter and set all others in lowercase

MySQL query to update all records to capitalize only the first letter and set all others in lowercase

Let us first create a table −

mysql> create table DemoTable2017
   -> (
   -> Name text
   -> );
Query OK, 0 rows affected (0.55 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable2017 values('JOHN SMITH,MYSQL');
Query OK, 1 row affected (0.13 sec)

mysql> insert into DemoTable2017 values('DAVID MILLER,MONGODB');
Query OK, 1 row affected (0.12 sec)

mysql> insert into DemoTable2017 values('CHRIS BROWN,JAVA');
Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable2017;

This will produce the following output −

+----------------------+
| Name                 |
+----------------------+
| JOHN SMITH,MYSQL     |
| DAVID MILLER,MONGODB |
| CHRIS BROWN,JAVA     |
+----------------------+
3 rows in set (0.00 sec)

Here is the query to set only the first letter in capital and rest in lower case −

mysql> update DemoTable2017
   -> set Name=REPLACE(CONCAT(UPPER(LEFT(Name, 1)), 
LOWER(SUBSTRING(Name, 2))),' , ', ', ');
Query OK, 3 rows affected (0.16 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Let us check the table records once again −

mysql> select *from DemoTable2017;

This will produce the following output −

+----------------------+
| Name                 |
+----------------------+
| John smith,mysql     |
| David miller,mongodb |
| Chris brown,java     |
+----------------------+
3 rows in set (0.00 sec)
Updated on: 2020-04-06T13:07:21+05:30

874 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements