How to Capitalize the First Letter of a String in MySQL
When working with text data in MySQL, you may encounter strings with inconsistent capitalization. For example, a database might contain values such as:
grizzliesmavericksCAVALIERSSpurshawKsnets
For reporting, dashboards, applications, and data-cleaning workflows, you may want to standardize these values so that only the first letter is uppercase and all remaining letters are lowercase:
GrizzliesMavericksCavaliersSpursHawksNets
MySQL does not have a built-in INITCAP() function like some other database systems. However, you can accomplish this by combining UCASE(), LOWER(), SUBSTRING(), and CONCAT().
The following query capitalizes the first character and converts the remaining characters to lowercase:
UPDATE athletesSET team = CONCAT( UCASE(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)));
This approach is particularly useful for standardizing names, product descriptions, categories, locations, and other text fields.
Example: How to Capitalize the First Letter in MySQL
Suppose we have a table named athletes containing information about basketball players:
CREATETABLE athletes ( id INTPRIMARYKEY, team TEXT NOTNULL, position TEXT NOTNULL, points INTNOTNULL);
We can insert some sample data:
INSERTINTO athletes VALUES(1, 'grizzlies', 'Guard', 15),(2, 'mavericks', 'Guard', 22),(3, 'CAVALIERS', 'Forward', 36),(4, 'Spurs', 'Guard', 18),(5, 'hawKs', 'Forward', 40),(6, 'nets', 'Forward', 25);
View the data using:
SELECT*FROM athletes;
The output is:
+----+-----------+----------+--------+| 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 team column contains inconsistent capitalization.
For example:
grizzliesis entirely lowercase.mavericksis entirely lowercase.CAVALIERSis entirely uppercase.Spursis already correctly formatted.hawKscontains mixed capitalization.netsis lowercase.
Suppose we want to standardize all of these values so that the first letter is uppercase and every other letter is lowercase.
Use CONCAT(), UCASE(), LOWER(), and SUBSTRING()
We can use:
UPDATE athletesSET team = CONCAT( UCASE(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)));
After running the query, the team column becomes:
+----+-----------+----------+--------+| 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 |+----+-----------+----------+--------+
The query has standardized the capitalization of every value.
How the MySQL Query Works
The query may initially look complicated:
UPDATE athletesSET team = CONCAT( UCASE(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)));
However, it consists of several simple operations.
Extract the First Character
The expression:
SUBSTRING(team, 1, 1)
extracts one character starting from position 1.
For example:
grizzlies → gmavericks → mCAVALIERS → CSpurs → ShawKs → hnets → n
Convert the First Character to Uppercase
Next:
UCASE(SUBSTRING(team, 1, 1))
converts that first character to uppercase.
For example:
g → Gm → MC → CS → Sh → Hn → N
Extract the Remaining Characters
The expression:
SUBSTRING(team, 2)
extracts the string starting from the second character.
For example:
grizzlies → rizzliesmavericks → avericksCAVALIERS → AVALIERSSpurs → purshawKs → awKsnets → ets
Convert the Remaining Characters to Lowercase
We then use:
LOWER(SUBSTRING(team, 2))
This converts everything after the first character to lowercase:
rizzlies → rizzliesavericks → avericksAVALIERS → avalierspurs → pursawKs → awksets → ets
Combine the Two Parts
Finally, CONCAT() joins the uppercase first character with the lowercase remainder:
CONCAT( UCASE(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)))
For example:
G + rizzlies = GrizzliesM + avericks = MavericksC + avaliers = CavaliersS + purs = SpursH + awks = HawksN + ets = Nets
A Simpler Way to Understand the Formula
You can think of the expression as:
uppercase(first character)+lowercase(all remaining characters)
In SQL:
CONCAT( UCASE(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)))
This effectively performs a proper-case transformation for a single-word string.
Preview the Changes Before Updating the Table
Because UPDATE permanently changes the stored values, it is often a good idea to preview the results first.
Instead of immediately running:
UPDATE athletesSET team = CONCAT( UCASE(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)));
you can first run:
SELECT team AS original_team, CONCAT( UCASE(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)) ) AS formatted_teamFROM athletes;
The result would look like:
+---------------+----------------+| original_team | formatted_team |+---------------+----------------+| grizzlies | Grizzlies || mavericks | Mavericks || CAVALIERS | Cavaliers || Spurs | Spurs || hawKs | Hawks || nets | Nets |+---------------+----------------+
This is a safer approach when cleaning production data because you can inspect the transformation before modifying the underlying table.
Capitalize the First Letter Without Updating the Table
If you only want to display the formatted value without changing the database, use SELECT instead of UPDATE:
SELECT CONCAT( UCASE(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)) ) AS formatted_teamFROM athletes;
This leaves the original team values unchanged.
This distinction is important:
SELECT
creates a formatted value only in the query result.
Whereas:
UPDATE
changes the stored data in the table.
Capitalize the First Letter for a Specific Row
You can also use a WHERE clause to update only selected records.
For example:
UPDATE athletesSET team = CONCAT( UCASE(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)))WHERE id =3;
This changes only the row where id = 3.
Capitalize the First Letter for Selected Teams
You could also apply the transformation to a particular team:
UPDATE athletesSET team = CONCAT( UCASE(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)))WHERE team ='CAVALIERS';
However, remember that the WHERE condition is evaluated against the existing value.
Using LOWER() and UPPER() in MySQL
MySQL provides functions for changing the case of strings.
LOWER() converts text to lowercase:
SELECT LOWER('CAVALIERS');Result:
cavaliers
UPPER() converts text to uppercase:
SELECT UPPER('grizzlies');Result:
GRIZZLIES
MySQL also supports LCASE() and UCASE() as equivalent functions:
LOWER() = LCASE()UPPER() = UCASE()
For example:
SELECT UPPER('mavericks');and:
SELECT UCASE('mavericks');both produce:
MAVERICKS
UCASE() vs UPPER()
Both can be used to capitalize the first character:
UCASE(SUBSTRING(team, 1, 1))
or:
UPPER(SUBSTRING(team, 1, 1))
For example:
SELECT CONCAT( UPPER(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)) ) AS formatted_teamFROM athletes;
This produces the same result.
Many developers prefer UPPER() because it clearly describes the operation, while UCASE() is also valid MySQL syntax.
Important: This Works Best for Single-Word Strings
The technique:
CONCAT( UPPER(SUBSTRING(column_name, 1, 1)), LOWER(SUBSTRING(column_name, 2)))
capitalizes only the first character of the entire string.
For example:
golden state warriors
becomes:
Golden state warriors
It does not become:
Golden State Warriors
If you need to capitalize the first letter of every word, the problem becomes more complex because MySQL does not provide a general built-in INITCAP() function.
For simple one-word values such as:
maverickscavaliersgrizzlies
the approach works very well.
Handle Empty Strings
An empty string contains no characters to capitalize.
For example:
SELECT CONCAT( UPPER(SUBSTRING('', 1, 1)), LOWER(SUBSTRING('', 2))) ASresult;returns an empty string.
If your data can contain empty strings, you may want to explicitly handle them:
SELECTCASEWHEN team =''THEN''ELSE CONCAT( UPPER(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)) )ENDAS formatted_teamFROM athletes;
Handle NULL Values
If the column can contain NULL, consider how you want those values handled.
For example:
SELECTCASEWHEN team ISNULLTHENNULLELSE CONCAT( UPPER(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)) )ENDAS formatted_teamFROM athletes;
This preserves NULL values.
If the column is defined as NOT NULL, as in the example table, this additional check isn’t necessary.
Use TRIM() Before Capitalizing
Real-world data may contain leading or trailing spaces.
For example:
" grizzlies "
You can use TRIM() first:
SELECT CONCAT( UPPER(SUBSTRING(TRIM(team), 1, 1)), LOWER(SUBSTRING(TRIM(team), 2)) ) AS formatted_teamFROM athletes;
This removes leading and trailing spaces before performing the capitalization.
For data-cleaning workflows, this can be useful when values come from CSV files, forms, APIs, or external databases.
Capitalize the First Letter of a Name
The same technique can be used for names.
Suppose you have:
johnMARYdavidsARAH
You can format them with:
SELECT CONCAT( UPPER(SUBSTRING(name, 1, 1)), LOWER(SUBSTRING(name, 2)) ) AS formatted_nameFROM customers;
The results become:
JohnMaryDavidSarah
Capitalize the First Letter of Product Names
This technique can also be useful for product data.
For example:
SELECT product_name, CONCAT( UPPER(SUBSTRING(product_name, 1, 1)), LOWER(SUBSTRING(product_name, 2)) ) AS formatted_productFROM products;
This can help standardize inconsistent product labels before displaying them in reports or applications.
However, be careful with brand names and acronyms. A transformation that forces everything after the first character to lowercase could incorrectly change names such as:
iPhoneeBaySQLAWSIBM
Therefore, automatic capitalization should be used only when the desired formatting rule is known.
Create a New Formatted Column Instead of Updating
If you don’t want to modify the original data, you can return both versions:
SELECT team AS original_team, CONCAT( UPPER(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)) ) AS formatted_teamFROM athletes;
This is often preferable when you want to preserve the original source data.
Complete MySQL Example
The following example can be copied and executed directly:
CREATETABLE athletes ( id INTPRIMARYKEY, team VARCHAR(100) NOTNULL, position VARCHAR(50) NOTNULL, points INTNOTNULL);INSERTINTO athletes VALUES(1, 'grizzlies', 'Guard', 15),(2, 'mavericks', 'Guard', 22),(3, 'CAVALIERS', 'Forward', 36),(4, 'Spurs', 'Guard', 18),(5, 'hawKs', 'Forward', 40),(6, 'nets', 'Forward', 25);-- Preview the formatted valuesSELECT team AS original_team, CONCAT( UPPER(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)) ) AS formatted_teamFROM athletes;-- Update the values permanentlyUPDATE athletesSET team = CONCAT( UPPER(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)));-- View the updated tableSELECT*FROM athletes;
After the UPDATE, the values become:
+----+-----------+----------+--------+| 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 |+----+-----------+----------+--------+
Frequently Asked Questions
How do I capitalize only the first letter in MySQL?
Use:
SELECT CONCAT( UPPER(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2))) AS formatted_teamFROM athletes;
How do I capitalize the first letter and lowercase the rest in MySQL?
Use:
CONCAT( UPPER(SUBSTRING(column_name, 1, 1)), LOWER(SUBSTRING(column_name, 2)))
This converts values such as:
CAVALIERS
to:
Cavaliers
Does MySQL have an INITCAP() function?
MySQL does not provide a general built-in INITCAP() function. You can combine UPPER(), LOWER(), SUBSTRING(), and CONCAT() to achieve the equivalent behavior for a single-word string.
How do I capitalize the first letter without changing the table?
Use SELECT:
SELECT CONCAT( UPPER(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2))) AS formatted_teamFROM athletes;
How do I permanently capitalize the first letter?
Use UPDATE:
UPDATE athletesSET team = CONCAT( UPPER(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2));
Make sure the parentheses are correctly closed:
UPDATE athletesSET team = CONCAT( UPPER(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)));
Can I use UCASE() instead of UPPER()?
Yes. These are equivalent for this purpose:
UPPER()
and:
UCASE()
For example:
SELECT CONCAT( UCASE(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)))FROM athletes;
Conclusion
MySQL does not have a simple built-in INITCAP() function for capitalizing the first character and converting the rest of a string to lowercase. However, you can easily accomplish this by combining UPPER(), LOWER(), SUBSTRING(), and CONCAT().
To format the team column, use:
UPDATE athletesSET team = CONCAT( UPPER(SUBSTRING(team, 1, 1)), LOWER(SUBSTRING(team, 2)));
This transforms values such as:
grizzliesmavericksCAVALIERShawKsnets
into:
GrizzliesMavericksCavaliersHawksNets
For production databases, it is a good practice to preview the transformation with SELECT before running UPDATE, especially when the column contains names, brands, acronyms, or other values where automatic case conversion may not always be appropriate.
