Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Finding Data that Matches a Pattern2:43 with Andrew Chalkley
You don't have to be that specific when searching a database table. You can use patterns of characters to help find those trickier bits of data you're trying to track down.
Placing the percent symbol (
%) any where in a string in conjunction with the
LIKE keyword will operate as a wildcard. Meaning it can be substituted by any number of characters, including zero!
SELECT <columns> FROM <table> WHERE <column> LIKE <pattern>;
SELECT title FROM books WHERE title LIKE "Harry Potter%Fire"; SELECT title FROM movies WHERE title LIKE "Alien%"; SELECT * FROM contacts WHERE first_name LIKE "%drew"; SELECT * FROM books WHERE title LIKE "%Brief History%";
PostgreSQL Specific Keywords
LIKE in PostgreSQL is case-sensitive. To case-insensitive searches do
SELECT * FROM contacts WHERE first_name ILIKE "%drew";
See all of the SQL used in SQL Basics in the SQL Basics Cheat Sheet.
The conditions we've been using so far have been very specific,
they've been exact values, like using the equality operator to find exact matches,
or the inequality operator to filter out exact matches.
But how about if we wanted to be a little bit more flexible with our searches?
Let's say a patron comes into the library, wanted to find a particular Harry Potter
book, but they can't remember which one they need to read next.
So you, as the librarian,
want to write a query that will search the database for all Harry Potter books.
We want to find all books where the title starts with Harry Potter.
We could try where title is equal to Harry Potter.
But nothing will match the text Harry Potter because it's a strict match.
We need to use a new keyword, like instead of the equality operator.
This still yields no results because the LIKE keyword needs to have at least
one wildcard to operate in the way we want it to.
A wildcard is a substitute character used when you don't know the rest of the text.
It can be placed anywhere in your search to help find different things.
In SQL, the percent sign means any number of characters, even zero.
So we'll add the percent at the end of Harry Potter, and this will return
all Harry Potter books, since they start with the pattern Harry Potter.
Now you can read the titles out to your forgetful patron.
Someone else comes in, and wants to know if we've got The Martian, by Andy Weir.
But you can't remember if it's just Martian, or The Martian.
So you can use a wildcard at the beginning of your search pattern.
That's right, it's The Martian.
Someone else comes into your library, and
wants to know if we've got any books about the universe, non fiction.
We can use two wildcards, oh and your search pattern is case insensitive,
meaning you don't need to include the correct upper casing or lower casing.
When we're doing exact matches we need to include the correct casing.
Like in the case of genre.
If you know the exact spelling of something, but not the capitalization,
you could still use the like keyword with no wildcards like this.
As you can see it brings back the same results.
Using the like key word with wild cards
can be a powerful way to search through data using patterns you already know.
You need to sign up for Treehouse in order to download course files.Sign up