Finding Data that Matches a Pattern

2: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.

Teacher's Notes
Video Transcript
Downloads

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.