LOWER()
SQL · String Functions
Theory
LOWER(string) returns a copy of the string with every letter converted to lowercase; digits, spaces and symbols are left unchanged. LOWER('Asha@TechTown.IN') returns 'asha@techtown.in'. The stored data is not changed unless you use LOWER inside an UPDATE.
Its main job is making comparisons case-insensitive. Whether 'Asha' = 'asha' is true depends on the database and its collation: MySQL and SQL Server are usually case-insensitive by default, while Postgres, Oracle and SQLite compare case-sensitively. Writing WHERE LOWER(email) = 'asha@techtown.in' gives the same answer everywhere. Grouping on LOWER(email) also exposes duplicates that differ only in case. One cost to know: applying a function to a column in WHERE can stop a normal index on that column from being used, so busy tables often store a normalised copy or use a function-based index.
Why we use it
Users type the same value in different cases: Asha@Gmail.com, asha@gmail.com, ASHA@GMAIL.COM. LOWER normalises them so searches, joins and duplicate checks treat them as the same value.
When to use it
Use LOWER for case-insensitive search, de-duplicating emails or usernames, and for normalising data before storing it. Use UPPER instead when the convention is upper case (codes, country codes). Some dialects offer ILIKE for case-insensitive pattern matching.
How it works
- Take the input string
- Read each character
- Is it an uppercase letter? Yes: Replace it with its lowercase form. No: Keep it unchanged.
- Repeat to the end of the string
- Return the lowercased copy
Syntax
LOWER(string)Example
SELECT LOWER(email) FROM employees;Key points
- Handy for case-insensitive comparisons and joins
- Pairs well with LIKE for search: LOWER(name) LIKE '%asha%'
- Only letters change; digits and symbols stay the same
- Default case sensitivity differs by database and collation
- LOWER(col) in WHERE may prevent normal index use