Pattern matching with LIKE

= only finds exact matches. LIKE finds text that fits a pattern.

The two wildcards

Starts with, ends with, contains

SELECT name FROM customers WHERE name LIKE 'L%';    -- starts with L

SELECT name FROM customers WHERE name LIKE '%n';    -- ends with n

SELECT name FROM products WHERE name LIKE '%or%';   -- contains "or"

Exactly one character: _

Cities with exactly five letters:

SELECT name, city
FROM customers
WHERE city LIKE '_____';

First names whose second letter is "a":

SELECT first_name, last_name
FROM employees
WHERE first_name LIKE '_a%';

Upper and lower case

In SQLite, LIKE ignores case for English letters, so '%monitor%' finds the 27" Monitor:

SELECT name FROM products WHERE name LIKE '%monitor%';

Databases differ here. MySQL usually ignores case too (it depends on the column's collation), while PostgreSQL's LIKE is case-sensitive and offers ILIKE for case-insensitive matching. A portable way to ignore case is to lower-case the column: WHERE LOWER(name) LIKE '%monitor%'.

NOT LIKE

SELECT first_name, title
FROM employees
WHERE title NOT LIKE '%Engineer%';

Searching for a literal % or _

To match the % character itself, pick an escape character with ESCAPE and put it in front of the wildcard:

SELECT
  '50% off'  LIKE '%\%%' ESCAPE '\' AS has_percent,
  'Half off' LIKE '%\%%' ESCAPE '\' AS also_has_percent;

The answers are 1 (true) and 0 (false): SQLite shows true and false as numbers.