Learn SQLite in Neovim: LIKE & Pattern Matching — Wildcards % and _ | Episode 9

Learn SQLite LIKE & Pattern Matching with Wildcards % and _

SQLite's LIKE operator enables flexible pattern matching in queries using wildcards. This tutorial demonstrates how to use the percent (%) and underscore (_) wildcards to find city names that start with, end with, contain, or match specific character patterns in a sample database of American cities.

Code

-- Create a table of cities
CREATE TABLE cities (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

-- Insert sample city names
INSERT INTO cities (name) VALUES
  ('San Francisco'),
  ('San Diego'),
  ('Los Angeles'),
  ('Nashville'),
  ('Jacksonville'),
  ('Portland'),
  ('Cleveland'),
  ('Orlando'),
  ('Cleveland'),
  ('Cleveland Heights');

-- Find cities that start with 'San'
SELECT name FROM cities WHERE name LIKE 'San%';

-- Find cities that end with 'ville'
SELECT name FROM cities WHERE name LIKE '%ville';

-- Find cities that contain 'land'
SELECT name FROM cities WHERE name LIKE '%land%';

-- Find cities with exactly one character between 'C' and 'e' (e.g., 'C_e')
SELECT name FROM cities WHERE name LIKE 'C_e%';

-- Exclude cities that contain 'land'
SELECT name FROM cities WHERE name NOT LIKE '%land%';

Key Points

• The LIKE operator allows pattern matching in WHERE clauses using wildcards.
• The percent sign (%) matches any sequence of zero or more characters.
• The underscore (_) matches exactly one character.
• Patterns like 'San%' find strings starting with "San", while '%ville' finds those ending with "ville".
• NOT LIKE excludes rows matching the specified pattern.