# LIKE

### Introduction to PostgreSQL LIKE operator <a href="#introduction-to-postgresql-like-operator" id="introduction-to-postgresql-like-operator"></a>

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:

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

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

```sql
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 <a href="#postgresql-like-operator-examples" id="postgresql-like-operator-examples"></a>

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

### 1) Basic LIKE operator examples <a href="#id-1-basic-like-operator-examples" id="id-1-basic-like-operator-examples"></a>

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:

```
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:

```
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 <a href="#id-2-using-the-like-operator-with-table-data" id="id-2-using-the-like-operator-with-table-data"></a>

The following example uses the `LIKE` operator to find customers whose first names contain the string `er`&#x20;

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

<figure><img src="/files/GfMJTs84eHSrct7E5Obk" alt=""><figcaption></figcaption></figure>

### 3) Using the LIKE operator a pattern that contains both wildcards <a href="#id-3-using-the-like-operator-a-pattern-that-contains-both-wildcards" id="id-3-using-the-like-operator-a-pattern-that-contains-both-wildcards"></a>

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;
```

<figure><img src="/files/OhLetqjwAfeYp92fYsBO" alt=""><figcaption></figcaption></figure>

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 <a href="#id-4-postgresql-not-like-examples" id="id-4-postgresql-not-like-examples"></a>

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;
```

<figure><img src="/files/XWUqkIVykQdqrRPIxuK3" alt=""><figcaption></figcaption></figure>

### PostgreSQL extensions of the LIKE operator <a href="#postgresql-extensions-of-the-like-operator" id="postgresql-extensions-of-the-like-operator"></a>

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%';
```

<figure><img src="/files/s8qoZe9jm1GHhUsrRjTk" alt=""><figcaption></figcaption></figure>

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:

```
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:

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

### PostgreSQL LIKE operator with ESCAPE option <a href="#postgresql-like-operator-with-escape-option" id="postgresql-like-operator-with-escape-option"></a>

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;
```

Creating Table,&#x20;

```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;
```

Output:

```
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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://courses.parottasalna.com/database-engineering/filtering-data/like.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.