> This page location: Filtering Data > LIKE
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL LIKE

**Info:** Pattern matching with LIKE, NOT LIKE, ILIKE, and ESCAPE works the same on any PostgreSQL database, so everything here applies wherever you run Postgres. If you're an enterprise looking for managed Postgres built for the AI era, [Lakebase](https://www.databricks.com/product/lakebase) delivers performance, security, and native integration with the Lakehouse so your operational and analytical data live together. If you're a developer or startup who needs to ship and scale fast, [Neon](https://neon.com) gives you the best Postgres platform with instant provisioning, branching, and serverless scale.

**Summary**: in this tutorial, you will learn how to use the PostgreSQL `LIKE` operator to query data based on patterns.

## Introduction to PostgreSQL LIKE operator

Suppose that you want to find customers, but you don't remember their names exactly. However, you can recall that their names begin with something like `Jen`.

How do you locate the exact customers from the database? You can identify customers in the  `customer` table by examining the first name column to see if any values begin with `Jen`. However, this process can be time-consuming, especially when the `customer` table has a large number of rows.

Fortunately, you can use the PostgreSQL `LIKE` operator to match the first names of customers with a string using the following query:

```sql
SELECT
  first_name,
  last_name
FROM
  customer
WHERE
  first_name LIKE 'Jen%';
```

Output:

```text
 first_name | last_name
------------+-----------
 Jennifer   | Davis
 Jennie     | Terry
 Jenny      | Castro
(3 rows)
```

The `WHERE` clause in the query contains an expression:

```
first_name LIKE 'Jen%'
```

The expression consists of the `first_name`, the `LIKE` operator and a literal string that contains a percent sign (`%`). The string `'Jen%'` is called a pattern.

The query returns rows whose values in the `first_name` column begin with `Jen` and are followed by any sequence of characters. This technique is called pattern matching.

You construct a pattern by combining literal values with wildcard characters and using the `LIKE` or `NOT LIKE` operator to find the matches.

PostgreSQL offers two wildcards:

- Percent sign (`%`) matches any sequence of zero or more characters.
- Underscore sign (`_`)  matches any single character.

Here's the basic syntax of the `LIKE` operator:

```sql
value LIKE pattern
```

The `LIKE` operator returns `true` if the `value` matches the `pattern`. To negate the `LIKE` operator, you use the `NOT` operator as follows:

```sql
value NOT LIKE pattern
```

The `NOT LIKE` operator returns `true` when the `value` does not match the `pattern`.

If the pattern does not contain any wildcard character, the `LIKE` operator behaves like the equal (`=`) operator.

## PostgreSQL LIKE operator examples

Let's take some examples of using the `LIKE` operator

### 1) Basic LIKE operator examples

The following statement uses the `LIKE` operator with a pattern that doesn't have any wildcard characters:

```sql
SELECT 'Apple' LIKE 'Apple' AS result;
```

Output:

```text
 result
--------
 t
(1 row)
```

In this example, the `LIKE` operator behaves like the equal to (`=`) operator. The query returns `true` because '`Apple' = 'Apple'` is `true`.

The following example uses the `LIKE` operator to match any string that starts with the letter `A`:

```sql
SELECT 'Apple' LIKE 'A%' AS result;
```

Output:

```text
 result
--------
 t
(1 row)
```

The query returns true because the string `'Apple'` starts with the letter `'A'`.

### 2) Using the LIKE operator with table data

We'll use the `customer` table from the [sample database](../postgresql-getting-started/postgresql-sample-database):

![customer table - PostgreSQL LIKE and ILIKE examples](https://neon.com/postgresqltutorial/customer.png)The following example uses the `LIKE` operator to find customers whose first names contain the string `er` :

```sql
SELECT
  first_name,
  last_name
FROM
  customer
WHERE
  first_name LIKE '%er%'
ORDER BY
  first_name;
```

Output:

```sql
first_name  |  last_name
-------------+-------------
 Albert      | Crouse
 Alberto     | Henning
 Alexander   | Fennell
 Amber       | Dixon
 Bernard     | Colby
...
```

### 3) Using the LIKE operator with a pattern that contains both wildcards

The following example uses the `LIKE` operator with a pattern that contains both the percent (`%`) and underscore (`_`) wildcards:

```sql
SELECT
  first_name,
  last_name
FROM
  customer
WHERE
  first_name LIKE '_her%'
ORDER BY
  first_name;
```

Output:

```text
 first_name | last_name
------------+-----------
 Cheryl     | Murphy
 Sherri     | Rhodes
 Sherry     | Marshall
 Theresa    | Watson
(4 rows)
```

The pattern `_her%` matches any strings that satisfy the following conditions:

- The first character can be anything.
- The following characters must be `'her'`.
- There can be any number (including zero) of characters after `'her'`.

### 4) PostgreSQL NOT LIKE examples

The following query uses the `NOT LIKE` operator to find customers whose first names do not begin with `Jen`:

```sql
SELECT
  first_name,
  last_name
FROM
  customer
WHERE
  first_name NOT LIKE 'Jen%'
ORDER BY
  first_name;
```

Output:

```text
 first_name  |  last_name
-------------+--------------
 Aaron       | Selby
 Adam        | Gooch
 Adrian      | Clary
 Agnes       | Bishop
...
```

## PostgreSQL extensions of the LIKE operator

PostgreSQL `ILIKE` operator, which is similar to the `LIKE` operator, but allows for **case-insensitive matching**. For example:

```sql
SELECT
  first_name,
  last_name
FROM
  customer
WHERE
  first_name ILIKE 'BAR%';
```

Output:

```text
 first_name | last_name
------------+-----------
 Barbara    | Jones
 Barry      | Lovelace
(2 rows)
```

In this example, the `BAR%` pattern matches any string that begins with `BAR`, `Bar`, `BaR`, and so on. If you use the `LIKE` operator instead, the query will return no row:

```sql
SELECT
  first_name,
  last_name
FROM
  customer
WHERE
  first_name LIKE 'BAR%';
```

Output:

```text
 first_name | last_name
------------+-----------
(0 rows)
```

PostgreSQL also provides some operators that mirror the functionality of `LIKE`, `NOT LIKE`, `ILIKE`, `NOT ILIKE`, as shown in the following table:

| Operator | Equivalent |
| -------- | ---------- |
| \~\~     | LIKE       |
| \~\~\*   | ILIKE      |
| !\~\~    | NOT LIKE   |
| !\~\~\*  | NOT ILIKE  |

For example, the following statement uses the `~~` operator to find a customer whose first names start with the string `Dar`:

```sql
SELECT
  first_name,
  last_name
FROM
  customer
WHERE
  first_name ~~ 'Dar%'
ORDER BY
  first_name;
```

Output:

```text
 first_name | last_name
------------+-----------
 Darlene    | Rose
 Darrell    | Power
 Darren     | Windham
 Darryl     | Ashcraft
 Daryl      | Larue
(5 rows)
```

## PostgreSQL LIKE operator with ESCAPE option

Sometimes, the data, that you want to match, contains the wildcard characters `%` and `_`. For example:

```
The rents are now 10% higher than last month
The new film will have _ in the title
```

To instruct the `LIKE` operator to treat the wildcard characters `%` and `_` as regular literal characters, you can use the `ESCAPE` option in the `LIKE` operator:

```sql
string LIKE pattern ESCAPE escape_character;
```

Let's [create a simple table](https://neon.com/postgresql/tutorial/postgresql-create-table) for demonstration:

```sql
CREATE TABLE t(
   message text
);

INSERT INTO t(message)
VALUES('The rents are now 10% higher than last month'),
      ('The new film will have _ in the title');

SELECT message FROM t;
```

Note that you'll learn how to [create a table](https://neon.com/postgresql/tutorial/postgresql-create-table) and [insert data into it](https://neon.com/postgresql/tutorial/postgresql-insert-multiple-rows) in the upcoming tutorials.

Output:

```text
                   message
----------------------------------------------
 The rents are now 10% higher than last month
 The new film will have _ in the title
(2 rows)
```

The following statement uses the `LIKE` operator with the `ESCAPE` option to treat the `%` followed by the number `10` as a regular character:

```sql
SELECT * FROM t
WHERE message LIKE '%10$%%' ESCAPE '$';
```

Output:

```
                   message
----------------------------------------------
 The rents are now 10% higher than last month
(1 row)
```

In the pattern `%10$%%`, the first and last `%` are the wildcard characters whereas the `%` appears after the escape character `$` is a regular character.

## Summary

- Use the `LIKE` operator to match data by patterns.
- Use the `NOT LIKE` operator to negate the `LIKE` operator.
- Use the `%` wildcard to match zero or more characters.
- Use the `_` wildcard to match a single character.
- Use the `ESCAPE` option to specify the escape character.
- Use the `ILIKE` operator to match data case-insensitively.

---

## Related docs (Filtering Data)

- [WHERE](https://neon.com/postgresql/tutorial/where)
- [AND Operator](https://neon.com/postgresql/tutorial/and)
- [OR Operator](https://neon.com/postgresql/tutorial/or)
- [LIMIT](https://neon.com/postgresql/tutorial/limit)
- [FETCH](https://neon.com/postgresql/tutorial/fetch)
- [IN](https://neon.com/postgresql/tutorial/in)
- [BETWEEN](https://neon.com/postgresql/tutorial/between)
- [IS NULL](https://neon.com/postgresql/tutorial/is-null)