On this page
Syntax
UPPER(string)UCASE(string) | Parameter | Type | Required | Description |
|---|---|---|---|
string |
string expression | yes | The value to convert. It can be a column, a literal or any expression that produces text. |
How it works
UPPER() takes a text value and returns a copy with each lowercase letter changed to its uppercase form. Digits, spaces, punctuation and symbols are left untouched, so UPPER('Order 42!') returns ORDER 42!. It is the mirror image of LOWER(), which converts to lowercase.
In MySQL you may also see UCASE(), which is simply an alias for UPPER() and behaves identically. Standard SQL and the other engines use UPPER(), so prefer UPPER() for portable code.
The most common use is normalising text so comparisons ignore case. By forcing both sides of a comparison to the same case you can match values such as Cairo, cairo and CAIRO as equal. It is also handy for tidying data before display, for example showing country codes or product codes in a consistent uppercase form.
Examples
Uppercase a column
SELECT name, UPPER(name) AS name_upper
FROM customers;name | name_upper -----+----------- Ali | ALI Sara | SARA Omar | OMAR
Case insensitive WHERE using UPPER on both sides
-- Match the email regardless of how it was typed
SELECT id, email
FROM users
WHERE UPPER(email) = UPPER('Mail@Mabbaz.com');id | email ---+---------------- 7 | mail@mabbaz.com
Uppercase for display
-- Show category codes in a consistent uppercase form
SELECT product, UPPER(category) AS category
FROM products;product | category ----------+--------- Keyboard | INPUT Monitor | DISPLAY Speaker | AUDIO
Using the UCASE alias in MySQL
-- UCASE is a MySQL only alias for UPPER
SELECT name, UCASE(name) AS name_upper
FROM customers;name | name_upper -----+----------- Ali | ALI Sara | SARA
Common mistakes
-- Wrapping the indexed column in UPPER stops
-- the optimiser from using an index on email
SELECT id, email
FROM users
WHERE UPPER(email) = 'MAIL@MABBAZ.COM';-- Store or compare with a case insensitive collation,
-- or add a functional index on UPPER(email) so the
-- lookup stays fast
CREATE INDEX idx_users_email_upper
ON users (UPPER(email));Applying UPPER() to an indexed column inside WHERE makes the predicate non sargable, so the database scans every row instead of seeking the index. Use a case insensitive collation, or add a functional index on UPPER(email) so the same expression can be looked up directly. See the indexing guide for why this matters.
-- This does NOT change what is stored.
-- The result is only in the query output.
SELECT UPPER(name) FROM customers;-- To actually change the stored values you must UPDATE
UPDATE customers
SET name = UPPER(name);UPPER() returns a new value for the result set; it never alters the data in the table. The stored rows stay exactly as they were unless you run an UPDATE that writes the uppercase value back.
Performance
UPPER() itself is cheap on a single value, but calling it on a column inside a WHERE or JOIN condition can be expensive. The expression is evaluated for every row, and it prevents the optimiser from using a plain index on that column, which often turns an index seek into a full table scan.
If you frequently compare text without caring about case, a case insensitive collation is usually faster and cleaner than wrapping columns in UPPER(). Where you must keep the function, most engines let you build a functional (expression) index on UPPER(column) so the lookup can seek instead of scan.
See the indexing guide for how sargable predicates and functional indexes keep case insensitive searches fast on large tables.
Interview questions
What does the UPPER() function do?
It returns the input string with every alphabetic character converted to uppercase. Non letter characters such as digits, spaces and punctuation are left unchanged, and NULL input returns NULL.
What is the difference between UPPER() and UCASE()?
There is none in behaviour. UCASE() is a MySQL alias for UPPER(). Standard SQL and other databases such as PostgreSQL, SQL Server and SQLite use UPPER(), so prefer UPPER() for portable code.
How do you perform a case insensitive comparison with UPPER()?
Apply UPPER() to both sides of the comparison so they are normalised to the same case, for example WHERE UPPER(email) = UPPER('Mail@Mabbaz.com'). A case insensitive collation achieves the same result without a function call.
SELECT id, email
FROM users
WHERE UPPER(email) = UPPER('Mail@Mabbaz.com');Does UPPER() change the data stored in the table?
No. It only transforms the value in the query result. The stored rows are unchanged unless you run an UPDATE statement that writes the uppercase value back to the column.
Why can wrapping a column in UPPER() slow down a query?
Because the predicate becomes non sargable: the database must evaluate UPPER(column) for every row and cannot use a plain index on that column. A case insensitive collation or a functional index on UPPER(column) restores fast lookups.