UPPER & LOWER
In this page:
Convert to Uppercase
The UPPER function converts all letters in a text string to capital letters. Any non-letter character remains unchanged, so numbers and punctuation pass through the function untouched.
Example: Convert to Uppercase
SELECT UPPER('hello world 123!') AS shouted;Convert to Lowercase
The LOWER function converts all uppercase letters in a text string into small letters. This is ideal for standardizing inputs, such as normalizing email addresses before checking them for duplicates.
Example: Convert to Lowercase
CREATE TABLE users (id INT, email TEXT);
INSERT INTO users VALUES (1, '[email protected]');
SELECT LOWER(email) AS normalized_email FROM users;Case Insensitive Comparisons
You can use LOWER or UPPER to do case-insensitive comparisons manually, especially when working with binary collation tables where MySQL would otherwise treat Apple and apple as different strings.
Example: Case Insensitive Comparisons
CREATE TABLE fruits (id INT, name TEXT);
INSERT INTO fruits VALUES (1, 'Apple'), (2, 'apple');
SELECT * FROM fruits WHERE LOWER(name) = LOWER('APPLE');Combining with CONCAT
UPPER and LOWER can be combined with CONCAT to create customized print messages and system alert values, such as building an uppercase warning label out of otherwise mixed-case data.
Example: Combining with CONCAT
CREATE TABLE alerts (id INT, level TEXT, message TEXT);
INSERT INTO alerts VALUES (1, 'warning', 'disk space low');
SELECT CONCAT(UPPER(level), ': ', message) AS alert_line FROM alerts;Formatting Output Names
You can clean up poorly formatted user records by applying UPPER and LOWER functions directly to your select lists, such as displaying names in Title Case-adjacent consistent casing without altering the stored data itself.
Example: Formatting Output Names
CREATE TABLE users (id INT, first_name TEXT);
INSERT INTO users VALUES (1, 'AMIT');
SELECT CONCAT(UPPER(LEFT(first_name, 1)), LOWER(SUBSTRING(first_name, 2))) AS clean_first_name FROM users;Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: