HOWTO · MySQL
How to MySQL Convert String to Lowercase
Learn how to convert strings to lowercase in MySQL using the LOWER() function. This article covers various methods, including updating columns and performing case-insensitive searches. Discover practical examples and enhance your database management skills today!
On this page
When working with text, you may want query results in a consistent case, search for a value regardless of how its letters were entered, or normalize stored data. MySQL provides LOWER() for converting a string to lowercase. The related name LCASE() performs the same conversion. In the examples below, SELECT expressions return lowercase values without changing stored rows, while the demonstrated UPDATE stores lowercase values in the table.
Convert a String in a SELECT Query
Use LOWER() in a SELECT expression to return a lowercase value while leaving the row unchanged. For example, with a friends table whose name values include AHSAN and SUBTAIN, the query is SELECT LOWER(name) AS lowercase_name FROM friends;. The returned values are ahsan and subtain. The alias labels the result column; it does not rename the stored column.
Run this small string-level illustration:
print("AHSAN".lower())
ahsan
This Python illustration shows the intended lowercase transformation for one string. It does not connect to MySQL or execute the SQL statement above; SQL examples in this article are syntax examples.
Use LCASE() as an Alias for LOWER()
LCASE() is an equivalent MySQL function name for LOWER(). For the same sample column, SELECT LCASE(name) AS lowercase_name FROM friends; returns lowercase values in the query result. Choose either spelling and use it consistently in a query or codebase. As with LOWER(), LCASE() does not modify the rows when it appears in a SELECT expression.
Use LOWER() in a WHERE Clause
To search by a normalized value, apply LOWER() to both the column and the search string: SELECT name FROM friends WHERE LOWER(name) = LOWER('ahsan');. This compares the lowercase expressions and can match a stored value such as AHSAN when the comparison rules permit it. Applying the function to both sides avoids relying on how the literal was capitalized.
Lowercasing does not make every comparison universally case-insensitive or accent-insensitive. MySQL comparison behavior also depends on the operands’ character sets and collations. Check the collation when matching rules matter, and consider the performance impact of applying a function to an indexed column; the expression may prevent a regular index from being used for the lookup.
Store Lowercase Values with UPDATE
A SELECT expression changes only what the query returns. To persist lowercase values, use an UPDATE, for example: UPDATE friends SET name = LOWER(name);. This changes every row in the table. First inspect the intended rows with a SELECT, and use a WHERE clause when only some rows should change, such as UPDATE friends SET name = LOWER(name) WHERE name IS NOT NULL;.
An update overwrites the original letter casing. Before applying it to production data, confirm the target rows and ensure the original values can be recovered if needed. Run the update in an appropriate transaction when your storage engine and application workflow support one. The SQL shown here is a pattern; its effect depends on the table, constraints, permissions, and transaction settings in your database.
Sort or Group Values Without Changing Stored Text
Use ORDER BY LOWER(name) to sort rows by their lowercase expressions while preserving the text in the table: SELECT name FROM friends ORDER BY LOWER(name);. The expression controls sort order; it does not rewrite name.
For grouping, SELECT LOWER(name) AS normalized_name, COUNT(*) AS total FROM friends GROUP BY LOWER(name); groups values by their lowercase expressions and counts rows in each group. The exact grouping equivalence follows MySQL’s collation rules, so values that differ in accents or other characters may compare according to the selected collation. Selecting the normalized expression makes the grouping key clear.
Key Behavior and Collation Limits
Use LOWER() or LCASE() in SELECT, WHERE, ORDER BY, or GROUP BY when you need lowercase expressions for a query. These expressions do not alter stored text. Use UPDATE only when changing stored values is the goal, and constrain the affected rows deliberately.
For searches, lowercasing both sides is a useful normalization pattern, but the column collation still influences comparisons. It can also affect sorting and grouping. If a query must follow a particular case or accent rule, choose and verify an appropriate collation for the column or comparison instead of assuming that lowercase conversion defines all matching behavior.