# 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="https://2781136461-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FK4KaCc7tzh0tRXwFV2A5%2Fuploads%2F64rEcdiygE1sMfyMsHR4%2Fimage.png?alt=media&#x26;token=598994cc-b641-43f4-bd75-d45b58d92da2" 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="https://2781136461-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FK4KaCc7tzh0tRXwFV2A5%2Fuploads%2Ft8ZVeeSemTAsDRYGvI0x%2Fimage.png?alt=media&#x26;token=0f03884f-ee60-4635-b270-e5f10f64c497" 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="https://2781136461-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FK4KaCc7tzh0tRXwFV2A5%2Fuploads%2FRbOKZJZqbvIs7vuvbVP7%2Fimage.png?alt=media&#x26;token=46902788-eda7-436a-9d46-6d88e1400aa0" 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="https://2781136461-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FK4KaCc7tzh0tRXwFV2A5%2Fuploads%2FfMm0MfXPl3Akli2yJKo4%2Fimage.png?alt=media&#x26;token=8adda880-5de3-4dfa-95d9-036ccba88d6d" 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.