Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

SQL Basics

Preview

Start a free Courses 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.

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    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, wanting 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 keyword with wildcards 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

      You need to sign up for Treehouse in order to set up Workspace

      Sign up