On this page
Syntax
LOWER(string)LCASE(string) | Parameter | Type | Required | Description |
|---|---|---|---|
string |
string expression | yes | The text value, column or expression to convert. Digits, spaces and punctuation are returned unchanged; only letters are folded to lower case. |
How it works
LOWER() takes a single string and returns a copy of it with all alphabetic characters folded to lower case. Non letter characters such as numbers, spaces and symbols pass through untouched, so LOWER('Order #42B') gives order #42b. Its counterpart is UPPER(), which does the reverse.
In MySQL the name LCASE() is an alias for LOWER(): the two behave identically, and LOWER() is the portable choice because it is the SQL standard name understood by PostgreSQL, SQL Server and SQLite as well. Prefer LOWER() in code you may move between engines.
The most common use is normalisation. Emails and usernames are usually meant to be case insensitive, so folding both sides of a comparison with LOWER() lets John.Doe@Shop.com match john.doe@shop.com. Many teams also store the value already lowered so lookups stay simple. Browse the full SQL functions library for related text helpers such as TRIM().
Examples
Lowercase a column
SELECT username, LOWER(username) AS username_lower
FROM users;username | username_lower =========+=============== JohnDoe | johndoe MaryJane | maryjane BOBSMITH | bobsmith
Normalise emails before comparing
SELECT id, email
FROM customers
WHERE LOWER(email) = LOWER('John.Doe@Shop.com');id | email ===+================== 7 | john.doe@shop.com
Store the normalised value on insert
INSERT INTO customers (email)
VALUES (LOWER('Sara.Ali@Mail.COM'));
SELECT email FROM customers WHERE id = LAST_INSERT_ID();email ================ sara.ali@mail.com
Case insensitive join and filter
SELECT o.id, u.username
FROM orders o
JOIN users u
ON LOWER(o.user_handle) = LOWER(u.username)
WHERE LOWER(u.username) = 'johndoe';id | username ===+========= 3 | JohnDoe 8 | JohnDoe
LCASE alias in MySQL
SELECT LCASE('HELLO World') AS lowered;lowered =========== hello world
Common mistakes
/* email is indexed, but wrapping it in LOWER()
forces a full scan of every row */
SELECT * FROM customers
WHERE LOWER(email) = 'john@shop.com';/* PostgreSQL: index the expression itself so the
lookup can still use an index only scan */
CREATE INDEX idx_customers_email_lower
ON customers (LOWER(email));
SELECT * FROM customers
WHERE LOWER(email) = 'john@shop.com';Calling LOWER() on an indexed column inside WHERE hides the column from the optimiser, so a plain index cannot be used and the query scans the table. Fix it with a functional index on LOWER(email), or store the column with a case insensitive collation so no function is needed. See the indexing guide for why this happens.
/* Expecting this to lower case the stored data.
It does not: the users table still holds the
original mixed case values. */
SELECT LOWER(username) FROM users;/* LOWER() returns a new value; to change what is
stored you must write it back with UPDATE. */
UPDATE users
SET username = LOWER(username);LOWER() never modifies the underlying rows. A SELECT only transforms the result set. Use UPDATE ... SET col = LOWER(col) when you actually want to change the stored data.
Performance
Applying LOWER() to a value in the SELECT list is cheap. The cost appears when you wrap an indexed column in it inside WHERE or JOIN, because the database can no longer match the plain index and falls back to scanning every row.
Two fixes keep such queries fast. The first is a functional index on the expression, for example CREATE INDEX ... ON customers (LOWER(email)), supported by PostgreSQL, SQLite and SQL Server (through a computed column). The second is a case insensitive collation on the column, which lets the engine compare values without any function call at all.
If you compare a column against constants often, storing the value already lowered is usually simplest: normalise once on insert, then compare directly against a plain index. Read the indexing guide for how an index only scan avoids touching the base table.
Interview questions
What does LOWER() do, and how does it differ from LCASE()?
LOWER() converts every letter in a string to lower case and leaves digits, spaces and punctuation unchanged. LCASE() is a MySQL alias that behaves identically. LOWER() is the SQL standard name and works across MySQL, PostgreSQL, SQL Server and SQLite, so it is the portable choice.
How do you perform a case insensitive comparison in SQL?
Fold both sides to the same case, for example WHERE LOWER(email) = LOWER(:input). Alternatively use a case insensitive collation on the column so no function is needed. Folding both sides is portable; a collation is often faster because it can still use an index.
SELECT id FROM customers
WHERE LOWER(email) = LOWER('John@Shop.com');Does LOWER() change the data stored in the table?
No. LOWER() returns a transformed copy of the value in the result set only. To change what is stored you must run an UPDATE, such as UPDATE users SET username = LOWER(username).
Why can LOWER() on a column slow down a query, and how do you fix it?
Wrapping an indexed column in LOWER() inside WHERE stops the optimiser from using the plain index, forcing a full scan. Fix it with a functional index on LOWER(column) or a case insensitive collation, or store the value already lowered.
What does LOWER() return when the input is NULL?
It returns NULL. Like most scalar string functions, LOWER() propagates NULL, so LOWER(NULL) is NULL rather than an empty string.