MySQL: How to Capitalize First Letter in Strings


You can use the following syntax to capitalize only the first letter in a string in MySQL:

UPDATE athletes
SET team = CONCAT(UCASE(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)));

This particular syntax capitalizes the first letter in each string in the team column of the athletes table.

The following example shows how to use this syntax in practice.

Example: How to Capitalize First Letter in Strings in MySQL

Suppose we have the following table named athletes that contains information about various basketball players:

-- create table 
CREATE TABLE athletes (
  id INT PRIMARY KEY,
  team TEXT NOT NULL,
  position TEXT NOT NULL,
  points INT NOT NULL
);

-- insert rows into table
INSERT INTO athletes VALUES (0001, 'grizzlies', 'Guard', 15);
INSERT INTO athletes VALUES (0002, 'mavericks', 'Guard', 22);
INSERT INTO athletes VALUES (0003, 'CAVALIERS', 'Forward', 36);
INSERT INTO athletes VALUES (0004, 'Spurs', 'Guard', 18);
INSERT INTO athletes VALUES (0005, 'hawKs', 'Forward', 40);
INSERT INTO athletes VALUES (0006, 'nets', 'Forward', 25);

-- view all rows in table
SELECT * FROM athletes;

Output:

+----+-----------+----------+--------+
| id | team      | position | points |
+----+-----------+----------+--------+
|  1 | grizzlies | Guard    |     15 |
|  2 | mavericks | Guard    |     22 |
|  3 | CAVALIERS | Forward  |     36 |
|  4 | Spurs     | Guard    |     18 |
|  5 | hawKs     | Forward  |     40 |
|  6 | nets      | Forward  |     25 |
+----+-----------+----------+--------+

Suppose that we would like to capitalize only the first letter of each string in the team column.

We can use the following syntax to do so:

UPDATE athletes
SET team = CONCAT(UCASE(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)));

Output:

+----+-----------+----------+--------+
| id | team      | position | points |
+----+-----------+----------+--------+
|  1 | Grizzlies | Guard    |     15 |
|  2 | Mavericks | Guard    |     22 |
|  3 | Cavaliers | Forward  |     36 |
|  4 | Spurs     | Guard    |     18 |
|  5 | Hawks     | Forward  |     40 |
|  6 | Nets      | Forward  |     25 |
+----+-----------+----------+--------+

Notice that the first letter of each string in the team column is now capitalized, with every other letter in each string converted to lowercase.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL:

MySQL: How to Check for Null in CASE Statement
MySQL: How to Use Case-Sensitive LIKE Search
MySQL: How to Use Case Statement with Multiple Conditions

Leave a Reply

Your email address will not be published. Required fields are marked *