---
title: "Pattern matching"
url: "https://docs.yugabyte.com/stable/develop/learn/text-search/pattern-matching/"
---

# Pattern matching

Learn how to search text with pattern matching

Learn how to search text using pattern matching

The `LIKE` operator is a basic pattern-matching operator that emulates wildcard-like matching similar to many \*nix shells. Pattern matching can be done either using `%` (percent) to match any sequence of characters, or `_`(underscore) to match any single character.

## Setup

- [![Local Cluster](/icons/database.svg) Local](#local)
- [![Cloud Cluster](/icons/cloud.svg) YugabyteDB Aeon](#cloud)
- [![Anywhere Cluster](/icons/server.svg) YugabyteDB Anywhere](#anywhere)

Setup

To set up a local universe, refer to [Set up a local YugabyteDB universe](/stable/explore/cluster-setup-local/).

Setup

To set up a cluster, refer to [Set up a YugabyteDB Aeon cluster](/stable/explore/cluster-setup-aeon/).

Setup

To create a universe, refer to [Create a multi-zone universe](/stable/yugabyte-platform/create-deployments/create-universe-multi-zone).

Create the following table:

```sql
CREATE TABLE IF NOT EXISTS words (
    id SERIAL,
    word TEXT NOT NULL,
    PRIMARY KEY(id)
);
```

Load some sample words into the table as follows:

```sql
INSERT INTO words(word) VALUES
  ('camp'),('carousel'),('cartel'),('carpet'),('carnivore'),('cartoon'),('carry'),('capsule'),
  ('corsica'),('medica'),('azteca'),('republica'),('chronica'),('orca'),('cathodically'),('capably'),
  ('cot'),('cat'),('cut'),('cwt'),('cit'),('cit'),('captainly'),('callously'),('career'),('calculate'),
  ('lychees'),('deer'),('peer'),('seer'),('breeze'),('green'),('teen'),('casually');
```

## Suffix matching

Add `%` to the end of a pattern to match any string that completes the given pattern. For example, to get all the words starting with `ca`, execute the following:

```sql
SELECT word FROM words WHERE word LIKE 'ca%' limit 5;
```

```output
      word
--------------
 carnivore
 camp
 capably
 cathodically
 cartoon
```

## Prefix matching

Add `%` to the beginning of a pattern to match any string that ends in the given pattern. For example, to get words ending with `ca`, execute the following:

```sql
SELECT word FROM words WHERE word LIKE '%ca' limit 5;
```

```output
   word
-----------
 azteca
 chronica
 republica
 corsica
 medica
```

## Infix matching

You can also use `%` to match any sequence of text between a given pattern. For example, to get all words starting with `ca` and ending in `ly`, execute the following:

```sql
SELECT word FROM words WHERE word LIKE 'ca%ly' limit 5;
```

```output
     word
--------------
 capably
 cathodically
 casually
 captainly
 callously
```

## Case insensitive matching

The `LIKE` operator performs case-sensitive matching. For example, if you change the pattern to uppercase, you may not get the same results.

```sql
SELECT word FROM words WHERE word LIKE 'C_T' limit 5;
```

```output
 word
------
(0 rows)
```

To support case-insensitive matching, use the `ILIKE` operator.

```sql
SELECT word FROM words WHERE word ILIKE 'C_T' limit 5;
```

```output
 word
------
 cit
 cot
 cut
 cat
 cit
```

## Regex matching

Use the `SIMILAR TO` operator to match patterns using the SQL standard's definition of a regular expression. SQL regular expressions are a cross between `LIKE` notation and common (`POSIX`) regular expression notation.

For example, to find all words that have `e` occurring three or more times consecutively, do the following:

```sql
SELECT word FROM words WHERE word SIMILAR TO '%e{2,}%' ;
```

```output
  word
---------
 peer
 green
 seer
 lychees
 deer
 teen
 breeze
 career
```

`SIMILAR TO` supports the following pattern-matching meta-characters:

- `|` denotes alternation (either of two alternatives).
- `*` denotes repetition of the previous item zero or more times.
- `+` denotes repetition of the previous item one or more times.
- `?` denotes repetition of the previous item zero or one time.
- `{m}` denotes repetition of the previous item exactly m times.
- `{m,}` denotes repetition of the previous item m or more times.
- `{m,n}` denotes repetition of the previous item at least m and not more than n times.

Use parentheses `()` to group items into a single logical item. A bracket expression `[...]` specifies a character class, just as in POSIX regular expressions.

## Single character matching

Use `_`(underscore) to match any single character. To get all the 3 letter words that start with `c` and end in `t`, execute the following:

```sql
SELECT word FROM words WHERE word LIKE 'c_t' limit 5;
```

```output
 word
------
 cit
 cot
 cut
 cat
 cit
```

## Learn more

- [Understand GIN indexes](/stable/explore/ysql-language-features/indexes-constraints/gin/ "Understand GIN indexes")
- [Advanced fuzzy matching in YugabyteDB](https://www.yugabyte.com/blog/fuzzy-matching-in-yugabytedb/ "Advanced fuzzy matching in YugabyteDB")
- [Optimizing LIKE/ILIKE with indexes](https://www.yugabyte.com/blog/postgresql-like-query-performance-variations/ "Optimizing LIKE/ILIKE with indexes")