Description

Login
Back

Beginner · 12 min

Text patterns

Find simple text patterns and understand wildcard matching.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Match a prefix

LIKE uses percent to match zero or more characters and underscore to match one character. SQLite LIKE is usually case-insensitive for ASCII letters; this behavior is not a portable rule for every database or language.

SELECT name FROM members WHERE name LIKE 'D%' ORDER BY id;

Output

name
Dee

Match a fixed pattern

A pattern with underscores specifies character positions. Parameter binding still matters when a user supplies a pattern; a pattern is data, not a reason to concatenate user input into SQL.

SELECT name FROM members WHERE name LIKE '_o' ORDER BY id;

Output

name
Bo

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Return member names that begin with A, ordered by ID.

Show solution
SELECT name FROM members WHERE name LIKE 'A%' ORDER BY id;

The prefix is fixed and the wildcard allows any remaining characters.

Practice