String Functions: Beginner
Text lives in VARCHAR columns
Single quotes mark a value, double quotes name a column
Text you type into a query goes inside single quotes: 'Paris'. Double quotes do a different job: they name a column or a table. "city" refers to the column called city, while 'city' is a 4-letter word. A query that wraps a value in double quotes asks for a column with that name, and when no such column exists the query fails before reading a single row.
| listing_id | title | literal_text | column_value |
|---|---|---|---|
| 1 | Sunny loft | Paris | Paris |
| 2 | Quiet studio | Paris | paris |
| 3 | O'Hara House | Paris | Dublin |
| 4 | Harbor view | Paris | Boston |
| 5 | Garden flat | Paris | Paris |
An apostrophe inside the text
Comparison is exact, character by character
SELECTlisting_id,cityFROM listingsWHERE city = 'Paris'
Digits stored as text are still text
| expression | result | why |
|---|---|---|
| '10' < '9' | true | '1' comes before '9', comparison stops there |
| 10 < 9 | false | numeric comparison of whole values |
| CAST('10' AS INTEGER) < 9 | false | the CAST makes it numeric |
| CAST('02110' AS INTEGER) | 2110 | the leading zero is lost |
| CAST(2110 AS VARCHAR) | '2110' | 4 characters of text |
| 'Paris' = 'paris' | false | P and p are different characters |
Each expression below compares 2 values. Which one returns true?
Quote the value, CAST the column
A value you compare against a VARCHAR column goes in single quotes: WHERE postal_code = '02110' keeps listing 4. Drop the quotes and the right side becomes the number 2110. A VARCHAR then meets an INTEGER, and the query stops with a type error. A text column you need as a number gets a CAST, and every row must then hold digits only: CAST(postal_code AS INTEGER) > 75000 fails on listing 3, because its code 'D02' has no integer to become.
| filter | what happens on listings |
|---|---|
| postal_code = '02110' | keeps listing 4 |
| postal_code = 02110 | type error: VARCHAR compared with INTEGER |
| CAST(postal_code AS INTEGER) > 75000 | error on listing 3: 'D02' cannot become an INTEGER |
| CAST(bedrooms AS VARCHAR) = '1' | keeps listings 1 and 5 |
CONCAT joins pieces of text in order
SELECThost_id,CONCAT(first_name, ' ', last_name) AS full_nameFROM hosts
Three hosts get a full name. Ken gets NULL, and the next heading explains why. The || operator does the same work 2 pieces at a time: first_name || ' ' || last_name reads left to right and produces an identical result. Choose whichever reads better in the query you are writing; the database treats them as one operation.
One NULL argument makes the whole result NULL
Supplying a default for the missing piece, so Ken shows as 'Ken' rather than NULL, uses a function from a later lesson. For now, IS NULL on the result finds the rows that need attention.
Numbers must become text before CONCAT accepts them
What does CONCAT('Ken', ' ', NULL) return?
Building a label piece by piece
A label that puts a first name in front of a number takes 3 arguments, and each one does a job. Leave out the separator and the name runs into the number. Leave out the CAST and the query fails before it reads a row. The table shows the 3 attempts on a single row, where listing_count is 12.
| expression | result on Amara's row |
|---|---|
| CONCAT(first_name, CAST(listing_count AS VARCHAR)) | 'Amara12' |
| CONCAT(first_name, ' ', listing_count) | type error: listing_count is an INTEGER |
| CONCAT(first_name, ' ', CAST(listing_count AS VARCHAR)) | 'Amara 12' |
LENGTH counts the characters in a string
SELECTguest_id,username,LENGTH(username) AS name_lenFROM guests
| expression | result |
|---|---|
| LENGTH('sky_walker') | 10 |
| LENGTH('jo') | 2 |
| LENGTH('') | 0 |
| LENGTH(NULL) | NULL |
| LENGTH('a b') | 3 |
| LENGTH(' hi ') | 6 |
| LENGTH('café') | 4 |
Characters, not bytes
LENGTH in WHERE finds malformed values
LENGTH with GROUP BY and aggregates
The 3 result rows can arrive in any order. A query with GROUP BY makes no promise about the sequence of its groups.
- ▸Spaces and punctuation count. LENGTH('a b') is 3 and LENGTH(' hi ') is 6.
- ▸The empty string has length 0 and NULL has length NULL, so a filter on LENGTH keeps or drops them differently.
- ▸The result is an INTEGER, so =, !=, <, > and IN all apply, and so do SUM, AVG, MIN and MAX.
What does LENGTH(' hi ') return, where the string has 2 spaces before hi and 2 spaces after it?
Filtering for values that are too short
LENGTH(username) < 3 keeps every username with fewer than 3 characters. Walk it through the guests table and NULL decides one of the outcomes. Guest 2's 'jo' measures 2 and passes. Guest 3's empty string measures 0 and passes too, so an empty value counts as too short. Guest 4's username is NULL, its length is NULL, and NULL < 3 is unknown, so WHERE drops the row. Guest 5's 'a b' measures 3, and 3 < 3 is false. A check for short values misses missing ones, and those need their own IS NULL check.
| guest_id | username | LENGTH(username) | LENGTH(username) < 3 |
|---|---|---|---|
| 1 | sky_walker | 10 | false |
| 2 | jo | 2 | true |
| 3 | '' | 0 | true |
| 4 | NULL | NULL | NULL |
| 5 | a b | 3 | false |
LOWER rewrites every letter as lowercase
| 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
SELECTsignup_id,FROM signupsWHERE LOWER(email) = '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
| signup_count | |
|---|---|
| Maria@Example.com | 1 |
| maria@example.com | 1 |
| KEN@example.com | 1 |
| amara@mail.org | 1 |
| Ken@Example.COM | 1 |
| email_norm | signup_count |
|---|---|
| maria@example.com | 2 |
| ken@example.com | 2 |
| amara@mail.org | 1 |
> 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?
UPPER is the mirror of LOWER
Codes are written in uppercase by convention
SELECTbooking_id,country_code,UPPER(country_code) AS codeFROM bookingsWHERE UPPER(country_code) IN ('US', 'CA')
Joining on a normalized key
- 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
- Country, currency and airport codes
- Product codes and ticket references
- Any column where the reference table stores uppercase
- 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
- 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
Capstone: totals per normalized country
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?
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.
> Back at Airbnb, the analyst takes the host's ticket and runs the duplicate finder: GROUP BY LOWER(email) with HAVING COUNT(*) > 1 returns maria@example.com with 2 accounts, confirming the split. A LENGTH check on the same table turns up 4 addresses shorter than 6 characters that can never be real, which go to a cleanup list. The merge request to engineering needs one line per host, so CONCAT builds 'Merge host 4471 into host 2208' from the 2 ids after a CAST. Finally the host's payouts depend on country, and the bookings table stores 'us' where the payout-region table stores 'US', so the join key becomes UPPER(country_code) and all 3 of her listings land in the right region.
Text arrives exactly as someone typed it; these 4 functions let you combine, measure and standardize it.
Making text behave since day one
- Category
- SQL
- Difficulty
- beginner
- Duration
- 29 minutes
- Challenges
- 21 hands-on challenges
Topics covered: String Data Type, CONCAT(), LENGTH(), LOWER(), UPPER()
Lesson Sections
- String Data Type (concepts: Basic Types)
Text lives in VARCHAR columns A VARCHAR column holds a sequence of characters: letters, digits, spaces and punctuation, in whatever order someone entered them. The database stores each character and attaches no meaning to it. A postal code of '75003' is 5 characters that happen to be digits, and the database compares it with other text the way it compares any string, character by character, without turning it into a number first. Single quotes mark a value, double quotes name a column An apostro
- CONCAT() (concepts: String Joining)
CONCAT joins pieces of text in order CONCAT takes 2 or more text arguments and returns 1 string made of them, in the order you listed them. It inserts nothing between the pieces: CONCAT('Maria', 'Lopez') returns 'MariaLopez'. A space is a piece of text like any other, so you pass it as its own argument, ' ', at the position where you want it to appear. The hosts table stores first and last names in separate columns, and a report wants one full name per host. One NULL argument makes the whole res
- LENGTH() (concepts: String Length)
LENGTH counts the characters in a string LENGTH(text) returns an INTEGER: the number of characters the string holds. Every character counts, including spaces and punctuation, so LENGTH('a b') is 3. LENGTH('') is 0, because the empty string exists and holds no characters. LENGTH(NULL) is NULL, because there is no string to measure. That split carries the empty string versus NULL distinction into measurement: an empty username has a size, a missing one has no size at all. The guests table has one
- LOWER() (concepts: 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. Nor
- UPPER() (concepts: 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 c