Text and Date Queries

LIKE Patterns

Matching Text

LIKE filters text with simple wildcard patterns. % means any run of characters.

Program

Play the script to select file names with a chosen pattern.

pattern
like_patterns.sql
Replay: real traced execution (multi-file project)
CREATE TABLE files (name TEXT);
INSERT INTO files VALUES ('readme.md'), ('notes.txt'), ('data_jan.csv'), ('data_feb.csv');
WITH params(pattern) AS (VALUES ('%.md')) SELECT name FROM files WHERE name LIKE (SELECT pattern FROM params) ORDER BY name;

CREATE TABLE files (name TEXT);
INSERT INTO files VALUES ('readme.md'), ('notes.txt'), ('data_jan.csv'), ('data_feb.csv');
WITH params(pattern) AS (VALUES ('data_%')) SELECT name FROM files WHERE name LIKE (SELECT pattern FROM params) ORDER BY name;

CREATE TABLE files (name TEXT);
INSERT INTO files VALUES ('readme.md'), ('notes.txt'), ('data_jan.csv'), ('data_feb.csv');
WITH params(pattern) AS (VALUES ('%.txt')) SELECT name FROM files WHERE name LIKE (SELECT pattern FROM params) ORDER BY name;

  1. tables ← 1 row

    1CREATE TABLE files (name TEXT);2INSERT INTO files VALUES ('readme.md'), ('notes.txt'), ('data_jan.csv'), ('data_feb.csv');
    values this step1 rowtables

  2. files ← 4 rows

    1CREATE TABLE files (name TEXT);2INSERT INTO files VALUES ('readme.md'), ('notes.txt'), ('data_jan.csv'), ('data_feb.csv');3WITH params(pattern) AS (VALUES ('%.md')) SELECT name FROM files WHERE name LIKE (SELECT pattern FROM params) ORDER BY name;
    values this step4 rowsfiles

  3. result ← 1 row

    2INSERT INTO files VALUES ('readme.md'), ('notes.txt'), ('data_jan.csv'), ('data_feb.csv');3WITH params(pattern) AS (VALUES ('%.md')) SELECT name FROM files WHERE name LIKE (SELECT pattern FROM params) ORDER BY name;
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE files (name TEXT);2INSERT INTO files VALUES ('readme.md'), ('notes.txt'), ('data_jan.csv'), ('data_feb.csv');
    values this step1 rowtables

  2. files ← 4 rows

    1CREATE TABLE files (name TEXT);2INSERT INTO files VALUES ('readme.md'), ('notes.txt'), ('data_jan.csv'), ('data_feb.csv');3WITH params(pattern) AS (VALUES ('data_%')) SELECT name FROM files WHERE name LIKE (SELECT pattern FROM params) ORDER BY name;
    values this step4 rowsfiles

  3. result ← 2 rows

    2INSERT INTO files VALUES ('readme.md'), ('notes.txt'), ('data_jan.csv'), ('data_feb.csv');3WITH params(pattern) AS (VALUES ('data_%')) SELECT name FROM files WHERE name LIKE (SELECT pattern FROM params) ORDER BY name;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE files (name TEXT);2INSERT INTO files VALUES ('readme.md'), ('notes.txt'), ('data_jan.csv'), ('data_feb.csv');
    values this step1 rowtables

  2. files ← 4 rows

    1CREATE TABLE files (name TEXT);2INSERT INTO files VALUES ('readme.md'), ('notes.txt'), ('data_jan.csv'), ('data_feb.csv');3WITH params(pattern) AS (VALUES ('%.txt')) SELECT name FROM files WHERE name LIKE (SELECT pattern FROM params) ORDER BY name;
    values this step4 rowsfiles

  3. result ← 1 row

    2INSERT INTO files VALUES ('readme.md'), ('notes.txt'), ('data_jan.csv'), ('data_feb.csv');3WITH params(pattern) AS (VALUES ('%.txt')) SELECT name FROM files WHERE name LIKE (SELECT pattern FROM params) ORDER BY name;
    values this step1 rowresult
LIKE `LIKE` compares text with a wildcard pattern.
% `%` matches zero or more characters.
pattern parameter The `params` CTE holds the selectable pattern.