lesson 06 Filtering & sorting · beginner

Lists & text patterns

Match lists of values and patterns in text.

the idea

IN matches any member of a list. LIKE matches text patterns: % means any number of characters and _ means one character. SQLite LIKE is case-insensitive for ASCII by default.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT name
FROM customers
WHERE name LIKE 'A%';

Watch for: An equality comparison with A% looks for a literal percent sign.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Match the start of a name. In LIKE, % stands for the rest of the name. It is not a character stored in the table.

   step-1.sql Run

   sql›

   SELECT name
   FROM customers;

2. 2

   Run the reference. IN matches any member of a list. LIKE matches text patterns: % means any number of characters and _ means one character. SQLite LIKE is case-insensitive for ASCII by default.

   step-2.sql Run

   sql›

   SELECT name
   FROM customers
   WHERE name LIKE 'A%';