Databases · Lesson 5.1
How a search box finds a name
So a mentor checking on two learners sent name IN Ada and Dee.
And it got back just Ada's row and Dee's row.
Because each name in the list must match the whole name, letter for letter.
Now say you type ada into Instagram's search box.
Instagram shows accounts as you type, like ada.codes and adams.photos.
But username equals ada keeps only an account called exactly ada.
And there's none.
So how does a search box find every name that starts with what you typed?
It uses LIKE, which checks text against a pattern instead of one whole value.
A pattern is text with stand-ins for the parts you don't know.
In a pattern, a percent sign stands for any number of characters, even none.
So Instagram's database might send this pattern: ada and a percent sign in single quotes.
It means ada, then anything.
ada.codes starts with ada, so it's kept.
adams.photos starts with ada too, so it's kept.
nada has ada in it, but it starts with n, so it's skipped.
ben has no ada at all, so it's skipped too.
Now how does Spotify find songs with Love anywhere in the title?
Its database might put a percent sign on each side of Love.
That means anything, then Love, then anything.
Love Story starts with Love, and the first percent sign matches nothing, so it's kept.
Crazy in Love ends with Love, and the last percent sign matches nothing, so it's kept.
Shape of You and Bad Blood have no Love in them, so they're skipped.
And how does a mentor on Simplified find a learner whose name starts with F?
The mentor types F into the chat's search box.
So Simplified's server might send this pattern: F and a percent sign in single quotes.
Fay starts with F, so Fay's row is kept.
Ada, Ben, Cy, Dee and Eli start with other letters, so they're skipped.
So a percent sign can match many characters, or none at all.
Simplified's users table holds six learners: Ada, Ben, Cy, Dee, Eli and Fay.
Its server sends Postgres SELECT name FROM users WHERE name LIKE '%e';.
How many rows pass the condition?
Explanation
First, '%e' means any number of characters, then an e at the very end.
Then, Dee ends in e, and Ben's e is in the middle, not at the end.
Ada, Cy, Eli and Fay end in other letters.
Therefore, 1 row passes: Dee.
Simplified's users table holds six learners, named Ada, Ben, Cy, Dee, Eli and Fay. Its server sends Postgres SELECT name FROM users WHERE name LIKE '%a%';.
How many rows does the result have?
Databases · Lesson 5.2
Exactly one more character
So when a mentor typed F, the pattern F and a percent sign found Fay.
Now say you type J into the Where from box on Google Flights.
It lists cities and airports together, like Jacksonville and JFK.
Say its database keeps them in one places table, and you want only the airport codes.
An airport code is always three letters, like JFK.
But the pattern J and a percent sign keeps the cities too.
Because a percent sign matches any number of characters.
So how do you ask for J and exactly two more characters?
In a pattern, an underscore stands for exactly one character.
So J and two underscores means J, then exactly two characters.
JFK is J, F, K, so it's kept.
And JAX is J, A, X, so it's kept too.
Jacksonville and Juneau have more than two characters after the J, so they're skipped.
LAX doesn't start with J, so it's skipped too.
Now what if you remember a friend's Instagram username as ben, then one more character?
You search for ben and one underscore.
bens is ben, then s, so it's kept.
ben has nothing after ben, so it's skipped.
ben.codes has six characters after ben, so it's skipped too.
aben starts with a, not ben, so it's skipped as well.
And how might Uber find its riders from states that start with N?
A US state code is always two letters, like NY for New York.
So Uber's database might send N and one underscore.
NY, NJ and NC are N, then one character, so they're kept.
CA and TX don't start with N, so they're skipped.
So each underscore matches exactly one character: never none, and never two.
Simplified's users table holds six learners: Ada, Ben, Cy, Dee, Eli and Fay.
Its server sends Postgres SELECT name FROM users WHERE name LIKE '__';, with two underscores.
How many rows pass the condition?
Explanation
First, each _ is exactly one character, so '__' means exactly two characters.
Then, Cy has two letters, and the other five names have three.
Therefore, 1 row passes: Cy.
Simplified's users table holds six learners, named Ada, Ben, Cy, Dee, Eli and Fay. Its server sends Postgres SELECT name FROM users WHERE name LIKE '___';, with three underscores.
How many rows does the result have?
Say an Instagram accounts table holds the usernames ada, adam, ada.x and nada. Its database gets SELECT username FROM accounts WHERE username LIKE 'ada_';.
Which username comes back?
Simplified's users table holds six learners, named Ada, Ben, Cy, Dee, Eli and Fay. Its server sends Postgres SELECT name FROM users WHERE name LIKE '_e%';.
How many rows does the result have?
Say Instagram's database keeps usernames, all in small letters.
Which condition keeps only the usernames that are exactly four characters long and start with b?
Say Uber's database keeps each rider's US ZIP code as text, always five digits, like 10001. It gets SELECT zip FROM riders WHERE zip LIKE '100__';.
Which of these ZIP codes comes back?
Simplified's users table holds six learners, named Ada, Ben, Cy, Dee, Eli and Fay. Its server sends Postgres SELECT name FROM users WHERE name LIKE '_y';.
How many rows does the result have?
Databases · Lesson 5.3
When capitals shouldn't matter
So when a mentor typed F, name LIKE F and a percent sign found Fay.
Now the mentor types eli, all in small letters, the way people type.
So the server sends the pattern eli and a percent sign.
But Eli's row isn't kept.
Why?
Because in Postgres, LIKE is case-sensitive: a capital and a small letter count as different.
Eli starts with a capital E, and the pattern starts with a small e.
So how does the mentor find Eli, whatever the capitals?
The server uses ILIKE instead of LIKE.
ILIKE matches a pattern just like LIKE, but ignores case.
Now what if you type Ada into Instagram's search box, with a capital A?
Every username in this accounts table is in small letters.
So LIKE, with Ada and a percent sign, keeps nothing.
Because ada.codes starts with a small a.
So Instagram's database might send ILIKE instead.
ILIKE ignores case, so ada.codes and adams.photos are kept.
nada and ben don't start with ada, so they're skipped.
And what if Spotify's songs table also holds lovely, by Billie Eilish, in small letters?
LIKE, with a percent sign on each side of Love, keeps Love Story and Crazy in Love.
But it skips lovely, because its l is small.
So Spotify's database might switch to ILIKE.
Love Story, Crazy in Love and lovely all have love in them, whatever the case.
Shape of You has no love at all, so it's still skipped.
Back on Simplified, the server can now send ILIKE for the mentor.
So ILIKE keeps Eli's row, because it matches Eli's capital E to the small e.
Simplified's users table holds six learners: Ada, Ben, Cy, Dee, Eli and Fay.
Its server sends Postgres SELECT name FROM users WHERE name ILIKE '%d%';.
How many rows pass the condition?
Explanation
First, '%d%' means a d anywhere, and ILIKE ignores case.
Then, Ada has a small d, and Dee starts with a capital D.
Ben, Cy, Eli and Fay have no d.
Therefore, 2 rows pass: Ada and Dee.
Say Spotify's songs table holds Love Story, Crazy in Love, lovely and Shape of You. Its database gets SELECT title FROM songs WHERE title ILIKE 'love%';.
Which titles come back?
Simplified's users table holds six learners, named Ada, Ben, Cy, Dee, Eli and Fay. Its server sends Postgres two queries: WHERE name LIKE '%E%' and WHERE name ILIKE '%E%'.
Which of the two keep Eli's row?
Simplified's users table holds six learners, named Ada, Ben, Cy, Dee, Eli and Fay. Its server sends Postgres SELECT name FROM users WHERE name LIKE '%e%';.
How many rows does the result have?
Simplified's users table holds six learners, named Ada, Ben, Cy, Dee, Eli and Fay. Its server sends Postgres SELECT name FROM users WHERE name ILIKE '_E_';, with a capital E.
How many rows does the result have?
Say an Instagram accounts table holds the usernames ben, benji.codes and ada.codes, all in small letters. Its database gets SELECT username FROM accounts WHERE username LIKE 'BEN%';.
What comes back?
Simplified's users table holds six learners, named Ada, Ben, Cy, Dee, Eli and Fay. Its server sends Postgres SELECT name FROM users WHERE name ILIKE '%e%';.
How many rows does the result have?