> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

> Reference material for ILIKE function

# ILIKE

Allows matching of strings based on comparison to a pattern. `ILIKE` is normally used as part of a `WHERE` clause. `ILIKE` is case-insensitive; use [LIKE](/reference-sql/functions-reference/string/like) for case-sensitive pattern matching. Note that Firebolt uses the `POSIX` locale, which means that it only classifies the ASCII letters "A" through "Z" and "a" through "z" as letters.

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
<expression> ILIKE '<pattern>'
```

## Parameters

| Parameter      | Description                                                                                                                              | Supported input types                                                                                                                                                                                       |
| :------------- | :--------------------------------------------------------------------------------------------------------------------------------------- | :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `<expression>` | Any expression that evaluates to `TEXT`                                                                                                  | `TEXT`                                                                                                                                                                                                      |
| `<pattern>`    | Specifies the pattern to match (case-insensitive). The pattern can be a constant or any `TEXT` expression, including a column reference. | `TEXT`. SQL wildcards are supported: <br /> <br />\* Use an underscore (`_`) to match any single character<br />\* Use a percent sign (`%`) to match any number of any characters, including no characters. |

## Return Type

`BOOLEAN`

## Example

Find nicknames from the `players` table that partially match the string "Joe" and any following characters as follows:

<div className="query-window">
  ```
  SELECT
  	playerid, nickname, email
  FROM
  	players
  WHERE
  	nickname ILIKE 'Joe%'
  ORDER BY playerid;
  ```

  | playerid <span>int null</span> | nickname <span>text null</span> | email <span>text null</span>                                  |
  | :----------------------------- | :------------------------------ | :------------------------------------------------------------ |
  | 145                            | joel53                          | [james96@example.com](mailto:james96@example.com)             |
  | 1356                           | joel35                          | [smithaustin@example.net](mailto:smithaustin@example.net)     |
  | 2823                           | joe12                           | [thompsondana@example.org](mailto:thompsondana@example.org)   |
  | 3232                           | joe30                           | [gabrielle32@example.org](mailto:gabrielle32@example.org)     |
  | 3872                           | joemata                         | [nashrodney@example.com](mailto:nashrodney@example.com)       |
  | 4655                           | joel68                          | [browntina@example.org](mailto:browntina@example.org)         |
  | 4880                           | joel47                          | [kristinarivas@example.com](mailto:kristinarivas@example.com) |

  <p><span>Rows: 7</span><span>Execution time: 2.46ms</span></p>
</div>

## Example using a non-constant pattern

The pattern doesn't have to be a literal. You can match each row against a pattern stored in another column. For example, given a `filters` table that holds one pattern per row, join it with `players` to return the nicknames that match any of the patterns:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT
    p.nickname, f.pattern
FROM
    players p
JOIN
    filters f ON p.nickname ILIKE f.pattern;
```

If the pattern column contains `JOE%` and `%LONG`, this returns nicknames such as `joedavis`, `joe79`, and `joellong` regardless of case.

## Unicode Behavior

Firebolt uses the `POSIX` locale, therefore `ILIKE` case insensitivity is limited to ASCII characters. The uppercase and lowercase versions of non-ASCII characters are not matched.

Returns `false` — `Æ` is non-ASCII, so `ENCYCLOPÆDIA` does not match `encyclopædia`:

<div className="query-window">
  ```
  SELECT 'ENCYCLOPÆDIA' ILIKE 'encyclopædia';
  ```

  | ?column? <span>boolean</span> |
  | :---------------------------- |
  | False                         |

  <p><span>Rows: 1</span><span>Execution time: 4.99ms</span></p>
</div>

Returns `true` — the non-ASCII `Æ` matches itself, and the ASCII letters match case-insensitively:

<div className="query-window">
  ```
  SELECT 'ENCYCLOPÆDIA' ILIKE 'encyclopÆdia';
  ```

  | ?column? <span>boolean</span> |
  | :---------------------------- |
  | True                          |

  <p><span>Rows: 1</span><span>Execution time: 4.86ms</span></p>
</div>

Returns `false` — `Ü` is non-ASCII, so `MÜNCHEN` does not match `München`:

<div className="query-window">
  ```
  SELECT 'MÜNCHEN' ILIKE 'München';
  ```

  | ?column? <span>boolean</span> |
  | :---------------------------- |
  | False                         |

  <p><span>Rows: 1</span><span>Execution time: 5.79ms</span></p>
</div>

Returns `true` — the non-ASCII `Ü` matches itself, and the ASCII letters match case-insensitively:

<div className="query-window">
  ```
  SELECT 'MÜNCHEN' ILIKE 'mÜnchen';
  ```

  | ?column? <span>boolean</span> |
  | :---------------------------- |
  | True                          |

  <p><span>Rows: 1</span><span>Execution time: 6.58ms</span></p>
</div>

Returns `false` — Greek letters are non-ASCII and are never case-folded:

<div className="query-window">
  ```
  SELECT 'Πσρ⋈' ILIKE 'πΣΡ%';
  ```

  | ?column? <span>boolean</span> |
  | :---------------------------- |
  | False                         |

  <p><span>Rows: 1</span><span>Execution time: 5.16ms</span></p>
</div>