SQL LOWER

What does the SQL LOWER function do?

Converts all alphabetic characters in a string expression to lowercase.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LOWER: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, DB2, Teradata

SQL LOWER Full Explanation

LOWER is a scalar string function defined in the SQL standard that returns a character value with every alphabetic character converted to its lowercase form. Non-alphabetic characters are left unchanged. The function is deterministic for single-byte character sets and most Unicode collations.LOWER is typically used to normalize text before comparison or storage. Because many databases perform case-sensitive comparisons by default, converting both operands to lowercase (or uppercase) ensures consistent, predictable matching. The function works on CHAR, VARCHAR, TEXT, and similar string types; most systems implicitly cast other data types to string if necessary.Collation and locale can affect results for characters whose lowercase representation differs between languages. For example, the Turkish dotted and dotless “I” require a Turkish collation to behave as expected. Multi-byte encodings (UTF-8, UTF-16) are fully supported in modern engines, but very old versions may only guarantee proper conversion for ASCII characters. LOWER returns NULL when the input expression is NULL.

SQL LOWER Syntax

LOWER(string_expression);

SQL LOWER Parameters

string_expression (STRING) - Any character or string expression to be converted to lowercase.

Example Queries Using SQL LOWER

-- Normalize email addresses before comparison
SELECT email, LOWER(email) AS email_lc
FROM users;

-- Case-insensitive search for the word "galaxy"
SELECT id, title
FROM articles
WHERE LOWER(title) LIKE '%galaxy%';

-- Remove duplicates ignoring case
SELECT DISTINCT LOWER(product_code) AS norm_code
FROM inventory;

Expected Output Using SQL LOWER

Each example returns the input string with alphabetic characters converted to lowercase, enabling case-insensitive matching or aggregation

Use Cases with SQL LOWER

Preparing data for case-insensitive joins or WHERE filters.
Normalizing email addresses, user names, or product codes before storage.
Eliminating duplicates that differ only by letter case.
Building search features that must ignore capitalization.
Formatting output text for consistent UI presentation.

Common Mistakes with SQL LOWER

Assuming LOWER makes comparisons case-insensitive without applying it to both operands.
Forgetting that NULL input returns NULL, which can exclude rows in filters.
Ignoring locale differences (e.g., Turkish I) that cause unexpected results.
Applying LOWER to binary or large object columns where implicit casting is not supported.

First Introduced In

SQL-92

Frequently Asked Questions

Does LOWER affect non-alphabetic characters?

No. It leaves numbers, punctuation, and symbols unchanged and converts only alphabetic characters to lowercase.

How do I perform a case-insensitive search with LOWER?

Apply LOWER to both the column and the search term: `WHERE LOWER(column_name) = LOWER('SearchTerm')` or use `LIKE`/`ILIKE` depending on the dialect.

Is LOWER safe for Unicode strings?

Modern databases fully support Unicode. However, ensure your collation handles language-specific rules (e.g., Turkish dotted I) to avoid surprises.

Can I index LOWER(column) to speed up queries?

Yes. Create a functional index such as `CREATE INDEX idx_users_email_lc ON users (LOWER(email));` to accelerate case-insensitive lookups.