Simplified
Simplified

L5.1How a search box finds a name

YOU

How a search box finds a name

SELECT name FROM usersWHERE name IN('Ada','Dee');usersnameAdaBenCyDeeEliFayAdaBenCyDeeEliFayresultname0 rowsAdaBenCyDeeEliFay?
adaada.codesadams.photos
SELECT username FROM accountsWHERE username='ada';SELECT username FROM accountsWHERE usernameLIKE'ada%';accountsusernameada.codesadams.photosnadabenada.codesadams.photosnadabenresultusername0 rowsada.codesadams.photosnadaben?
SELECT title FROM songsWHERE titleLIKE'%Love%';songstitleLove StoryCrazy in LoveShape of YouBad BloodLove StoryCrazy in LoveShape of YouBad Bloodresulttitle0 rowsLove StoryCrazy in LoveShape of YouBad Blood?
SELECT name FROM usersWHERE name LIKE'F%';usersnameAdaBenCyDeeEliFayAdaBenCyDeeEliFayFresultname0 rowsAdaBenCyDeeEliFay?

Databases · Lesson 5.1

Matching Text

How a search box finds a name

Simplified
0:00 / 2:04

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.

SELECT name, xp FROM usersWHERE name IN ('Ada', 'Dee');usersnamexpAda120Ben40Cy0Dee300Eli40Fay75resultnamexpAda120Dee300

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.

SELECT username FROM accountsWHERE username='ada';SELECT username FROM accountsWHERE usernameLIKE'ada%';accountsusernameada.codesadams.photosnadabenada.codesadams.photosnadabenresultusername0 rowsada.codesadams.photosnadaben

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.

SELECT username FROM accountsWHERE username='ada';SELECT username FROM accountsWHERE usernameLIKE'ada%';accountsusernameada.codesadams.photosnadabenada.codesadams.photosnadabenresultusername0 rowsada.codesadams.photosnadaben

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.

SELECT title FROM songsWHERE titleLIKE'%Love%';songstitleLove StoryCrazy in LoveShape of YouBad BloodLove StoryCrazy in LoveShape of YouBad Bloodresulttitle0 rowsLove StoryCrazy in LoveShape of YouBad Blood

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.

SELECT name FROM usersWHERE name LIKE'F%';usersnameAdaBenCyDeeEliFayAdaBenCyDeeEliFayresultname0 rowsAdaBenCyDeeEliFay

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.

Example: How a search box finds a name

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.

Question 1

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?

rows

Exactly one more character

SELECT name FROM usersWHERE name LIKE'F%';usersnameAdaBenCyDeeEliFayAdaBenCyDeeEliFayresultname0 rowsAdaBenCyDeeEliFay?
Where from?JJacksonvilleJFK
SELECT name FROM placesWHERE nameLIKE'J%';SELECT name FROM placesWHERE nameLIKE'J__';placesnameJFKJacksonvilleJAXJuneauLAXJFKJacksonvilleJAXJuneauLAXresultname0 rowsJFKJacksonvilleJAXJuneauLAX?
SELECT username FROM accountsWHERE usernameLIKE'ben_';accountsusernamebenbensben.codesabenbenbensben.codesabenresultusername0 rowsbenbensben.codesaben?
SELECT state FROM ridersWHERE stateLIKE'N_';ridersstateNYCANJTXNCNYCANJTXNCresultstate0 rowsNYCANJTXNC?

Databases · Lesson 5.2

Matching Text

Exactly one more character

Simplified
0:00 / 1:54

So when a mentor typed F, the pattern F and a percent sign found Fay.

SELECT name FROM usersWHERE name LIKE'F%';usersnameAdaBenCyDeeEliFayAdaBenCyDeeEliFayresultname0 rowsAdaBenCyDeeEliFay

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.

SELECT name FROM placesWHERE nameLIKE'J%';SELECT name FROM placesWHERE nameLIKE'J__';placesnameJFKJacksonvilleJAXJuneauLAXJFKJacksonvilleJAXJuneauLAXresultname0 rowsJFKJacksonvilleJAXJuneauLAX

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.

SELECT name FROM placesWHERE nameLIKE'J%';SELECT name FROM placesWHERE nameLIKE'J__';placesnameJFKJacksonvilleJAXJuneauLAXJFKJacksonvilleJAXJuneauLAXresultname0 rowsJFKJacksonvilleJAXJuneauLAX

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.

SELECT username FROM accountsWHERE usernameLIKE'ben_';accountsusernamebenbensben.codesabenbenbensben.codesabenresultusername0 rowsbenbensben.codesaben

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.

SELECT state FROM ridersWHERE stateLIKE'N_';ridersstateNYCANJTXNCNYCANJTXNCresultstate0 rowsNYCANJTXNC

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.

Example: Exactly one more character

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.

Question 1

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?

rows

Question 2

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?

Question 3

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?

rows

Question 4

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?

Question 5

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?

Question 6

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?

rows

When capitals shouldn't matter

SELECT name FROM usersWHERE name LIKE'F%';usersnameAdaBenCyDeeEliFayAdaBenCyDeeEliFayresultname0 rowsAdaBenCyDeeEliFay?
SELECT name FROM usersWHERE nameLIKE'eli%';SELECT name FROM usersWHERE nameILIKE'eli%';usersnameAdaBenCyDeeEliFayAdaBenCyDeeEliFayeliresultname0 rowsAdaBenCyDeeEliFay?
SELECT username FROM accountsWHERE usernameLIKE'Ada%';SELECT username FROM accountsWHERE usernameILIKE'Ada%';accountsusernameada.codesadams.photosnadabenada.codesadams.photosnadabenAdaresultusername0 rowsada.codesadams.photosnadaben?
SELECT title FROM songsWHERE titleLIKE'%Love%';SELECT title FROM songsWHERE titleILIKE'%love%';songstitleLove StoryCrazy in LoveShape of YoulovelyLove StoryCrazy in LoveShape of Youlovelyresulttitle0 rowsLove StoryCrazy in LoveShape of Youlovely?
SELECT name FROM usersWHERE nameLIKE'eli%';SELECT name FROM usersWHERE nameILIKE'eli%';usersnameAdaBenCyDeeEliFayAdaBenCyDeeEliFayresultname0 rowsAdaBenCyDeeEliFay?

Databases · Lesson 5.3

Matching Text

When capitals shouldn't matter

Simplified
0:00 / 1:42

So when a mentor typed F, name LIKE F and a percent sign found Fay.

SELECT name FROM usersWHERE name LIKE'F%';usersnameAdaBenCyDeeEliFayAdaBenCyDeeEliFayresultname0 rowsAdaBenCyDeeEliFay

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.

SELECT name FROM usersWHERE nameLIKE'eli%';SELECT name FROM usersWHERE nameILIKE'eli%';usersnameAdaBenCyDeeEliFayAdaBenCyDeeEliFayresultname0 rowsAdaBenCyDeeEliFay

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.

LIKEEli≠'eli%'

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.

ILIKEEli='eli%'

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.

SELECT username FROM accountsWHERE usernameLIKE'Ada%';SELECT username FROM accountsWHERE usernameILIKE'Ada%';accountsusernameada.codesadams.photosnadabenada.codesadams.photosnadabenresultusername0 rowsada.codesadams.photosnadaben

So Instagram's database might send ILIKE instead.

SELECT username FROM accountsWHERE usernameLIKE'Ada%';SELECT username FROM accountsWHERE usernameILIKE'Ada%';accountsusernameada.codesadams.photosnadabenada.codesadams.photosnadabenresultusername0 rowsada.codesadams.photosnadaben

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.

SELECT title FROM songsWHERE titleLIKE'%Love%';SELECT title FROM songsWHERE titleILIKE'%love%';songstitleLove StoryCrazy in LoveShape of YoulovelyLove StoryCrazy in LoveShape of Youlovelyresulttitle0 rowsLove StoryCrazy in LoveShape of Youlovely

So Spotify's database might switch to ILIKE.

SELECT title FROM songsWHERE titleLIKE'%Love%';SELECT title FROM songsWHERE titleILIKE'%love%';songstitleLove StoryCrazy in LoveShape of YoulovelyLove StoryCrazy in LoveShape of Youlovelyresulttitle0 rowsLove StoryCrazy in LoveShape of Youlovely

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.

SELECT name FROM usersWHERE nameLIKE'eli%';SELECT name FROM usersWHERE nameILIKE'eli%';usersnameAdaBenCyDeeEliFayAdaBenCyDeeEliFayresultname0 rowsAdaBenCyDeeEliFay

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.

Example: When capitals shouldn't matter

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.

Question 1

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?

Question 2

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?

Question 3

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?

rows

Question 4

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?

rows

Question 5

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?

Question 6

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?

rows
Assistant

Highlight lesson text and click Ask AI, or type a question below.