Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Start a free Basic trial
to watch this video
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.
SQL Used
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>;
Examples:
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 ILIKE.
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, 0:00 they've been exact values, like using the equality operator to find exact matches, 0:04 or the inequality operator to filter out exact matches. 0:10 But how about if we wanted to be a little bit more flexible with our searches? 0:14 Let's say a patron comes into the library, wanted to find a particular Harry Potter 0:19 book, but they can't remember which one they need to read next. 0:23 So you, as the librarian, 0:27 want to write a query that will search the database for all Harry Potter books. 0:29 We want to find all books where the title starts with Harry Potter. 0:34 We could try where title is equal to Harry Potter. 0:38 But nothing will match the text Harry Potter because it's a strict match. 0:49 We need to use a new keyword, like instead of the equality operator. 0:54 This still yields no results because the LIKE keyword needs to have at least 0:59 one wildcard to operate in the way we want it to. 1:04 A wildcard is a substitute character used when you don't know the rest of the text. 1:08 It can be placed anywhere in your search to help find different things. 1:14 In SQL, the percent sign means any number of characters, even zero. 1:18 So we'll add the percent at the end of Harry Potter, and this will return 1:24 all Harry Potter books, since they start with the pattern Harry Potter. 1:28 Now you can read the titles out to your forgetful patron. 1:33 Someone else comes in, and wants to know if we've got The Martian, by Andy Weir. 1:37 But you can't remember if it's just Martian, or The Martian. 1:41 So you can use a wildcard at the beginning of your search pattern. 1:44 That's right, it's The Martian. 1:50 Someone else comes into your library, and 1:53 wants to know if we've got any books about the universe, non fiction. 1:55 We can use two wildcards, oh and your search pattern is case insensitive, 2:00 meaning you don't need to include the correct upper casing or lower casing. 2:05 When we're doing exact matches we need to include the correct casing. 2:09 Like in the case of genre. 2:14 If you know the exact spelling of something, but not the capitalization, 2:16 you could still use the like keyword with no wildcards like this. 2:20 As you can see it brings back the same results. 2:32 Using the like key word with wild cards 2:36 can be a powerful way to search through data using patterns you already know. 2:38
You need to sign up for Treehouse in order to download course files.
Sign up