2.1 Filtering with LIKE, REGEXP, BETWEEN, IN, Date and Time

WHERE filters rows. Think of it as the gate of a result set: rows that satisfy the condition pass through; the rest are blocked.

LIKE: simple text matching

SELECT name
FROM students
WHERE name LIKE 'A%';

% means any sequence of characters. _ usually means one arbitrary character.

Common patterns:

• 'A%': starts with A.
• '%a%': contains a.
• '__a%': the third character is a.

REGEXP: regex matching is product-sensitive

Regex is useful for more complex text patterns, such as "starts with A through D". But regex syntax in SQL is not unified.

For example, to find students whose names start with A, B, C, or D:

-- Common MySQL / MariaDB form
SELECT name, country
FROM students
WHERE name REGEXP '^[A-D]';

-- Common PostgreSQL form
SELECT name, country
FROM students
WHERE name ~ '^[A-D]';

Here ^ means the start of the text, and [A-D] means any one character from A through D. With sample rows such as Ada, Bo, Cora, and Devi, all four names match this pattern.

Product note:

• MySQL commonly uses: name REGEXP '^[A-D]'.
• PostgreSQL commonly uses: name ~ '^[A-D]'.
• SQLite does not include complete REGEXP behavior by default; it usually needs an extension or user-defined function.
• SQL Server usually does not provide the same REGEXP operator and often needs another approach.

BETWEEN and IN

SELECT name, score
FROM students
WHERE score BETWEEN 80 AND 95;

BETWEEN usually includes both boundary values, like score >= 80 AND score <= 95.

SELECT name, country
FROM students
WHERE country IN ('US', 'CA', 'GB');

IN checks whether a value belongs to a set. If the list is very long, consider joining to a table instead of writing a huge literal list.

Date and time

SELECT name, enrolled_on
FROM students
WHERE enrolled_on BETWEEN DATE '2026-02-01' AND DATE '2026-03-31';

For date queries, be precise about column types and boundaries. If the column is a timestamp, 2026-03-31 may mean midnight at the start of that day, not every time on that date.