SQL · Theory

LIKE

Theory

50/439

LIKE

LIKE matches a pattern. % = any characters. _ = one character. WHERE name LIKE 'A%'; names starting with A. WHERE name LIKE '%sha'; ending with sha. WHERE name LIKE 'A_ha'; A + one char + ha.

LIKE is not regex. For regex use REGEXP. LIKE is case-insensitive for typical utf8mb4_ci collations — don’t fight it with LOWER() unless you must.

Leading % (LIKE '%sha') cannot use a normal B-tree index well. Search ‘starts with’ when you can: 'A%'.

Trap — LIKE 40 without quotes on a numeric thought. Trap: * as wildcard (that is not SQL).

On the example next to this theory — LIKE — wHERE combines predicates with AND/OR; LIKE does pattern match.

Exam tip

WHERE name LIKE 'A%'; — starts with A.

LIKE — sample query

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES (1, 'Pen', 10), (2, 'Book', 200), (3, 'Bag', 50);
SELECT * FROM products
WHERE price BETWEEN 20 AND 150 AND name LIKE 'B%';

LIKE — wHERE combines predicates with AND/OR; LIKE does pattern match.

Short notes

DefLIKE 'A%' starts with A. % many chars, _ one char.
RuleNot regex. Leading % is hard to index.
TrapUsing * as a wildcard.

SQL · Theory

LIKE

← All stacks

Theory

50/439

LIKE

LIKE matches a pattern. % = any characters. _ = one character. WHERE name LIKE 'A%'; names starting with A. WHERE name LIKE '%sha'; ending with sha. WHERE name LIKE 'A_ha'; A + one char + ha.

LIKE is not regex. For regex use REGEXP. LIKE is case-insensitive for typical utf8mb4_ci collations — don’t fight it with LOWER() unless you must.

Leading % (LIKE '%sha') cannot use a normal B-tree index well. Search ‘starts with’ when you can: 'A%'.

Trap — LIKE 40 without quotes on a numeric thought. Trap: * as wildcard (that is not SQL).

On the example next to this theory — LIKE — wHERE combines predicates with AND/OR; LIKE does pattern match.

Exam tip

WHERE name LIKE 'A%'; — starts with A.

LIKE — sample query

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES (1, 'Pen', 10), (2, 'Book', 200), (3, 'Bag', 50);
SELECT * FROM products
WHERE price BETWEEN 20 AND 150 AND name LIKE 'B%';

LIKE — wHERE combines predicates with AND/OR; LIKE does pattern match.

Short notes

DefLIKE 'A%' starts with A. % many chars, _ one char.
RuleNot regex. Leading % is hard to index.
TrapUsing * as a wildcard.

LIKE

LIKE — sample query

Short notes

Questions

LIKE

LIKE — sample query

Short notes

Questions