Filter Rows By Pattern With LIKE

Match string columns against a wildcard pattern using `LIKE`.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Find all users with an example.com email address

The % before @example.com matches any username prefix of any length. Carol's test.org address does not match and is excluded. Use ILIKE in PostgreSQL for a case-insensitive pattern match.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE users (id INT, email VARCHAR(100));

INSERT INTO
  users (id, email)
VALUES
  (1, '[email protected]'),
  (2, '[email protected]'),
  (3, '[email protected]');

Validated query Shared across supported engines.

SQL

SELECT
  id,
  email
FROM
  users
WHERE
  email LIKE '%@example.com'
ORDER BY
  id;

Expected result Returned rows for the shared example.

id	email
1	[email protected]
2	[email protected]

Output is identical across all engines.

Useful when

Where this command helps.

searching for rows where a column matches a wildcard pattern
finding records by partial string match such as domain or prefix

Explanation

What the command is doing.

LIKE matches strings using two wildcards: % matches any sequence of zero or more characters, and _ matches exactly one character. It is case-insensitive in MySQL and SQL Server by default, but case-sensitive in PostgreSQL. NOT LIKE excludes matching rows.

Filter Rows By Pattern With LIKE

Find all users with an example.com email address

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.