# SQL LIKE: Match Text Patterns With Wildcards

- URL: https://betteranalyst.com/sql/like

SQL guide · Filtering

The `LIKE` operator compares a text value against a pattern. Two wildcards give it flexibility: `%` matches any sequence of characters, including none, and `_` matches exactly one character. Everything else in the pattern must match literally.

It is the standard tool for prefix, suffix and contains searches, such as finding names that start with a letter or emails from one domain. Add `NOT` to find values that do not fit the pattern. This guide also covers what older material calls the SQL LIKE query or SQL-LIKE syntax; they are the same operator.

Pattern position determines speed. A prefix pattern like `'Sm%'` can use an ordinary index on the column, but a leading wildcard like `'%son'` usually forces a scan of every row. For heavy text search, a full-text index or a trigram index is the better fit.

## Basic syntax

```
SELECT column1
FROM table_name
WHERE column LIKE 'pattern';

WHERE column NOT LIKE 'pattern'
WHERE column LIKE 'pattern' ESCAPE '\'
```

`%` stands for zero or more characters and `_` for exactly one. To search for a literal percent or underscore, define an escape character with `ESCAPE` and place it before the wildcard. Patterns are text, so numeric columns are implicitly converted in some databases.

## SQL LIKE examples

### Starts with, ends with, contains

The position of the percent sign sets the meaning. The three queries find names beginning with A, email addresses ending with a domain, and product names containing a word.

```
SELECT * FROM customers WHERE name LIKE 'A%';
SELECT * FROM customers WHERE email LIKE '%@example.com';
SELECT * FROM products  WHERE name LIKE '%wireless%';
```

### Single character wildcard

Each underscore stands for one character. This matches five-character codes that start with AB and end with 9, such as AB129 or ABX89 but not AB19.

```
SELECT product_id, sku
FROM products
WHERE sku LIKE 'AB__9';
```

### NOT LIKE

Exclude rows that match a pattern. This removes internal test accounts whose email starts with test.

```
SELECT customer_id, email
FROM customers
WHERE email NOT LIKE 'test%';
```

### Matching a literal percent sign

To find a real percent character, declare an escape character and place it before the wildcard. This finds discount descriptions that contain the text 20%.

```
SELECT promo_id, description
FROM promotions
WHERE description LIKE '%20!%%' ESCAPE '!';
```

## Where SQL dialects differ

**PostgreSQL:** `LIKE` is case sensitive. Use `ILIKE` for case-insensitive matching, `~` for regular expressions, and a `pg_trgm` index to speed up `%contains%` searches.

**MySQL:** Case sensitivity follows the column collation, which is usually case insensitive. Use `LIKE BINARY` to force a case-sensitive match. The default escape character is a backslash.

**SQL Server:** Case follows the collation. Square brackets work as character classes in patterns, for example `LIKE '[A-C]%'` or `'[^0-9]%'`, which standard SQL lacks.

**SQLite:** `LIKE` is case insensitive for ASCII characters by default. Use `GLOB` for case-sensitive matching with Unix-style wildcards `*` and `?`.

**Oracle:** Case sensitive by default. Use `UPPER(col) LIKE` or `REGEXP_LIKE` for flexible matching. There is no default escape character.

## Common mistakes

- Starting a pattern with `%` on a large table and wondering why it is slow. A leading wildcard normally prevents index use.
- Using `=` with wildcards, as in `name = 'A%'`. Wildcards only work with `LIKE`.
- Forgetting that `_` is a wildcard, so searching for `snake_case` also matches `snakeXcase`. Escape the underscore.
- Assuming case rules are the same everywhere. A query that matches in MySQL may return nothing in PostgreSQL.
- Not trimming trailing spaces in fixed-width `CHAR` columns, so a pattern without a trailing `%` fails to match.

## Related SQL guides

- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL NOT](https://betteranalyst.com/sql/not)
- [SQL IN](https://betteranalyst.com/sql/in)
- [SQL AND](https://betteranalyst.com/sql/and)
- [SQL OR](https://betteranalyst.com/sql/or)
- [SQL SELECT](https://betteranalyst.com/sql/select)

[Browse all SQL guides](https://betteranalyst.com/sql)

## Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

[Try the AI SQL generator](https://betteranalyst.com/ai-sql-query-generator)