Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.

Preview

Start a free Basic trial
to watch this video

Sign up for Treehouse

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.

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.

  • 0:00

    The conditions we've been using so far have been very specific,

  • 0:04

    they've been exact values, like using the equality operator to find exact matches,

  • 0:10

    or the inequality operator to filter out exact matches.

  • 0:14

    But how about if we wanted to be a little bit more flexible with our searches?

  • 0:19

    Let's say a patron comes into the library, wanted to find a particular Harry Potter

  • 0:23

    book, but they can't remember which one they need to read next.

  • 0:27

    So you, as the librarian,

  • 0:29

    want to write a query that will search the database for all Harry Potter books.

  • 0:34

    We want to find all books where the title starts with Harry Potter.

  • 0:38

    We could try where title is equal to Harry Potter.

  • 0:49

    But nothing will match the text Harry Potter because it's a strict match.

  • 0:54

    We need to use a new keyword, like instead of the equality operator.

  • 0:59

    This still yields no results because the LIKE keyword needs to have at least

  • 1:04

    one wildcard to operate in the way we want it to.

  • 1:08

    A wildcard is a substitute character used when you don't know the rest of the text.

  • 1:14

    It can be placed anywhere in your search to help find different things.

  • 1:18

    In SQL, the percent sign means any number of characters, even zero.

  • 1:24

    So we'll add the percent at the end of Harry Potter, and this will return

  • 1:28

    all Harry Potter books, since they start with the pattern Harry Potter.

  • 1:33

    Now you can read the titles out to your forgetful patron.

  • 1:37

    Someone else comes in, and wants to know if we've got The Martian, by Andy Weir.

  • 1:41

    But you can't remember if it's just Martian, or The Martian.

  • 1:44

    So you can use a wildcard at the beginning of your search pattern.

  • 1:50

    That's right, it's The Martian.

  • 1:53

    Someone else comes into your library, and

  • 1:55

    wants to know if we've got any books about the universe, non fiction.

  • 2:00

    We can use two wildcards, oh and your search pattern is case insensitive,

  • 2:05

    meaning you don't need to include the correct upper casing or lower casing.

  • 2:09

    When we're doing exact matches we need to include the correct casing.

  • 2:14

    Like in the case of genre.

  • 2:16

    If you know the exact spelling of something, but not the capitalization,

  • 2:20

    you could still use the like keyword with no wildcards like this.

  • 2:32

    As you can see it brings back the same results.

  • 2:36

    Using the like key word with wild cards

  • 2:38

    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