Table of Contents
In MySQL, the function CONCAT and SUBSTRING can be combined to capitalize the first letter of a string. The CONCAT function is used to merge two or more strings, while the SUBSTRING function extracts a portion of a string. By using SUBSTRING to extract the first letter of the string and CONCAT to merge it with the rest of the string, the first letter can be capitalized. This can be helpful when manipulating data in a database, such as ensuring consistent formatting for names or titles.
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: