UPPER()
Concepts covered: Case Conversion
UPPER is the mirror of LOWER
UPPER(text) turns every lowercase letter in the string into its capital and leaves digits, spaces and punctuation where they were. UPPER('us') is 'US', UPPER('75003-b') is '75003-B', and UPPER(NULL) is NULL. Every rule from the LOWER section holds with the direction reversed: apply it to both sides of a comparison, repeat the expression in GROUP BY, and keep the literal you compare against in the same case as the function produces.
Codes are written in uppercase by convention
Country codes, currency codes, airport codes and stock tickers are published in capitals: US, EUR, JFK. Reference tables store them that way, and so does any list you write in an IN clause. Data that people type arrives in whatever case they used, so a bookings table may hold 'us', 'US' and 'Us' for the same country. UPPER on the column makes all 3 match the uppercase list, and the filter keeps every booking from the countries you asked for.
SELECT
booking_id,
country_code,
UPPER(country_code) AS code
FROM bookings
WHERE UPPER(country_code) IN ('US', 'CA')| booking_id | country_code |
|---|---|
| 101 | us |
| 7 | US |
| 23 | ca |
| 4 | Us |
| 58 | mx |
Four bookings pass and the Mexican one does not. Without UPPER, only booking 7 would pass, because 'US' is the only spelling that matches the list character for character.
Joining on a normalized key
The join lesson showed that a join pairs rows whose keys are equal, and equality on text is exact. A countries table stores its code in uppercase, so a join on the raw bookings column pairs 1 of the 3 US bookings and silently drops the other 2. Wrapping the bookings side in UPPER makes every pair that should match, match. The countries side needs no wrapping because its values are already uppercase, but wrapping both sides costs nothing and protects you when that assumption stops being true.
SELECT
b.booking_id,
b.country_code,
c.country_name
FROM bookings b
INNER JOIN countries c
ON UPPER(b.country_code) = c.code| booking_id | country_code | nights |
|---|---|---|
| 101 | us | 3 |
| 7 | US | 2 |
| 23 | ca | 5 |
| 4 | Us | 1 |
| 58 | mx | 4 |
| code | country_name |
|---|---|
| US | United States |
| CA | Canada |
| MX | Mexico |
| booking_id | country_code | country_name |
|---|---|---|
| 101 | us | United States |
| 7 | US | United States |
| 23 | ca | Canada |
| 4 | Us | United States |
| 58 | mx | Mexico |
- Reach for LOWER
- Email addresses and usernames, which people read in lowercase
- Free text such as search terms and tags
- Any column where the stored convention is lowercase
- Reach for UPPER
- Country, currency and airport codes
- Product codes and ticket references
- Any column where the reference table stores uppercase
- Do
- Apply the same function to both sides of a comparison or join key
- Write literals in the case the function produces: UPPER(code) = 'US'
- Repeat the full expression in GROUP BY, never the alias
- Pick one convention per column and use it in every query
- Avoid
- Compare UPPER(code) against a lowercase literal; it can never be true
- Expect UPPER or LOWER to remove spaces or punctuation
- Forget that UPPER(NULL) is NULL and will not match any code
- Mix UPPER in one query and LOWER in another for the same column
Capstone: a booking reference from 3 functions
Support wants a reference like US-101 for every booking: the uppercase country code, a hyphen, and the booking number. That takes UPPER for the code, CAST to turn the INTEGER id into text, and CONCAT to assemble the 3 pieces. The support form accepts references of at most 6 characters, so LENGTH runs alongside to check each one. Predict the 5 references and their lengths before you run it: the country part is always 2 characters and the hyphen is 1, so the length depends only on how many digits the id has. Then check whether any reference breaks the limit.
SELECT
booking_id,
CONCAT(UPPER(country_code), '-', CAST(booking_id AS VARCHAR)) AS booking_ref,
LENGTH(CONCAT(UPPER(country_code), '-', CAST(booking_id AS VARCHAR))) AS ref_len
FROM bookings| booking_id | country_code | nights |
|---|---|---|
| 101 | us | 3 |
| 7 | US | 2 |
| 23 | ca | 5 |
| 4 | Us | 1 |
| 58 | mx | 4 |
| booking_id | booking_ref | ref_len |
|---|---|---|
| 101 | US-101 | 6 |
| 7 | US-7 | 4 |
| 23 | CA-23 | 5 |
| 4 | US-4 | 4 |
| 58 | MX-58 | 5 |
Capstone: totals per normalized country
Finance wants nights booked per country. GROUP BY country_code would report US 3 times with 3, 2 and 1 nights, which reads as 3 countries. GROUP BY UPPER(country_code) folds the spellings and reports 1 row for the United States with 6 nights. The expression appears in the SELECT list and in GROUP BY in the same form, as it did with LOWER.
SELECT
UPPER(country_code) AS code,
COUNT(*) AS booking_count,
SUM(nights) AS total_nights
FROM bookings
GROUP BY UPPER(country_code)| booking_id | country_code | nights |
|---|---|---|
| 101 | us | 3 |
| 7 | US | 2 |
| 23 | ca | 5 |
| 4 | Us | 1 |
| 58 | mx | 4 |
| code | booking_count | total_nights |
|---|---|---|
| CA | 1 | 5 |
| MX | 1 | 4 |
| US | 3 | 6 |
Three rows come back: US with 3 bookings and 6 nights, CA with 1 booking and 5 nights, MX with 1 booking and 4 nights. The nights still add up to 15, the total the raw table holds, because normalizing moved rows between groups without adding or losing any.
Nested functions run from the inside out
Both capstones put one function inside another. The database evaluates the innermost call first and hands its result to the call around it. For booking 101 the ref_len column takes 4 steps: UPPER('us') gives 'US', CAST(101 AS VARCHAR) gives '101', CONCAT joins 'US', '-' and '101' into 'US-101', and LENGTH counts 6. The outermost function decides the type of the result, so ref_len holds INTEGERs while booking_ref holds text.
What does LENGTH(CONCAT(UPPER('ab'), ' ', LOWER('CD'))) return?
SELECT
LENGTH(CONCAT(UPPER('ab'), ' ', LOWER('CD'))) AS result- 4, because the space between the 2 pieces is not counted
- 5, because 'AB cd' holds 2 letters, a space and 2 more letters
- NULL, because UPPER and LOWER cannot be nested inside CONCAT
- 'AB cd', because CONCAT runs last and returns text
WHERE combines with the normalized grouping. Adding WHERE UPPER(country_code) IN ('US', 'MX') removes the Canadian booking before any group forms, and the query returns 2 rows: US with 6 nights and MX with 4. The list is written in capitals because UPPER produces capitals; a list of ('us', 'mx') would match no row at all.
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.