LOWER()
Concepts covered: Case Conversion
LOWER rewrites every letter as lowercase
LOWER(text) returns a copy of the string with each uppercase letter replaced by its lowercase form. Digits, spaces, punctuation and letters that were already lowercase pass through unchanged, so the result has the same length as the input. LOWER('Maria@Example.com') is 'maria@example.com'. LOWER(NULL) is NULL, as with every function in this lesson. The stored column stays as it was; the query works on a transformed copy that exists only in the result.
| expression | result |
|---|---|
| LOWER('Maria@Example.com') | 'maria@example.com' |
| LOWER('KEN') | 'ken' |
| LOWER('75003-B') | '75003-b' |
| LOWER('already lower') | 'already lower' |
| LOWER('') | '' |
| LOWER(NULL) | NULL |
Normalize both sides before you compare
The signups table holds 5 rows. Two of them belong to Ken, who typed his address once in capitals and once with a capital K and a capital domain. WHERE email = 'ken@example.com' matches neither, because the first section showed that = checks every character's case. LOWER on the left side turns both of Ken's spellings into the same string, and the comparison against a lowercase literal then finds both rows.
SELECT
signup_id,
email
FROM signups
WHERE LOWER(email) = 'ken@example.com'| signup_id | |
|---|---|
| 1 | Maria@Example.com |
| 2 | maria@example.com |
| 3 | KEN@example.com |
| 4 | amara@mail.org |
| 5 | Ken@Example.COM |
The literal on the right side is already lowercase, and that matters as much as the function on the left. If someone writes LOWER(email) = 'Ken@example.com' the comparison can never be true: the left side has no uppercase letters and the right side has one. When the right side is a column instead of a literal, wrap it too: LOWER(a.email) = LOWER(b.email) is the shape to use for a join key.
Normalize before you group
GROUP BY looks at exact values, so GROUP BY email makes 5 groups out of 5 rows and reports that every address appears once. GROUP BY LOWER(email) folds the spellings together first and makes 3 groups, which is the count a person would give. The expression has to appear in GROUP BY and in the SELECT list in the same form, because the database groups on what GROUP BY names and then shows what SELECT asks for.
Add HAVING COUNT(*) > 1 and the query becomes a duplicate finder: it returns each normalized address that appears more than once and how many times. This is how the analyst in the intro finds the split: Maria@Example.com and maria@example.com fold into maria@example.com with a count of 2, and Ken's 2 spellings fold the same way.
SELECT
LOWER(email) AS email_norm,
COUNT(*) AS signup_count
FROM signups
GROUP BY LOWER(email)
HAVING COUNT(*) > 1| signup_id | |
|---|---|
| 1 | Maria@Example.com |
| 2 | maria@example.com |
| 3 | KEN@example.com |
| 4 | amara@mail.org |
| 5 | Ken@Example.COM |
| email_norm | signup_count |
|---|---|
| ken@example.com | 2 |
| maria@example.com | 2 |
Put the duplicate finder together yourself. The tiles below hold every piece of the query plus 2 that do not belong: one groups by the alias, which GROUP BY cannot see, and one tries to filter an aggregate in WHERE, which runs before the groups exist.
Build the query that returns each lowercase email address appearing more than once in signups, with the number of times it appears.
A function inside an aggregate runs on every row
When a function sits inside an aggregate, it runs once per row and the aggregate sees only the results. Turn the nesting around and the order turns with it: the outer function runs once, on the single value the aggregate returns. On the 5 listing titles the 2 orders give different numbers, because MAX(title) picks the title that comes last in character order, and that title is the shortest one.
| expression | first step | result |
|---|---|---|
| MAX(LENGTH(title)) | lengths 10, 12, 12, 11, 11 | 12 |
| LENGTH(MAX(title)) | MAX picks 'Sunny loft' | 10 |
The signups table has 5 rows holding 3 distinct addresses once case is ignored. Which expression returns 3?
- COUNT(DISTINCT email), since DISTINCT already merges the spellings
- COUNT(LOWER(email)), since LOWER merges the spellings before counting
- COUNT(DISTINCT LOWER(email)), since DISTINCT sees the lowercase copies
- LOWER(COUNT(DISTINCT email)), since LOWER applies to the final count
About this lesson
This section is part of the String Functions: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.