Converts text to lowercase.
LOWER(<text>)
Makes every letter lowercase; other characters don't change. Its mirror function is UPPER, which makes letters uppercase. Most often you need LOWER to compare text regardless of case: Emma, emma and EMMA become the same.
employees
| name | department | salary | bonus | phone |
|---|---|---|---|---|
| Emma | backend | 1200 | 300 | +44 7700 900123 |
| Liam | backend | 1000 | NULL | NULL |
| Olivia | frontend | 1350 | 150 | +44 7700 900456 |
| Noah | frontend | 950 | NULL | +44 7700 900789 |
| Sophia | design | 1200 | 200 | NULL |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| lower_case |
|---|
| hello, sql |
| name | department |
|---|---|
| Emma | backend |
Whether someone typed EMMA or emma, we bring both sides to lowercase, and Emma is found.
| emma.wilson@example.com |
People often type addresses any old way, but it's easier to store and compare them in lowercase.
Bring both sides to the same case: LOWER(name) = 'Emma' finds nobody, because the left side is all lowercase and the right one has a capital E.
A regular index on name won't help a query with LOWER(name). For such searches you create an index on the expression: CREATE INDEX ON employees (LOWER(name));.
For case-insensitive pattern matching there's ILIKE: name ILIKE 'em%'.