---
title: SQL regular expression functions
description: Use regular expression functions to operate on data in SQL queries.
url: https://docs.influxdata.com/influxdb3/enterprise/reference/sql/functions/regular-expression/
estimated_tokens: 3636
product: InfluxDB 3 Enterprise
version: enterprise
---

# SQL regular expression functions

The InfluxDB 3 Enterprise SQL implementation uses the [PCRE-like](https://en.wikibooks.org/wiki/Regular_Expressions/Perl-Compatible_Regular_Expressions) regular expression [syntax](https://docs.rs/regex/latest/regex/#syntax) (excluding some features such as look-around and back-references) and supports the following regular expression functions:

-   [regexp\_count](#regexp_count)
-   [regexp\_like](#regexp_like)
-   [regexp\_match](#regexp_match)
-   [regexp\_replace](#regexp_replace)

## regexp\_count

Returns the number of matches that a regular expression has in a string.

```sql
regexp_count(str, regexp[, start, flags])
```

### Arguments

-   **str**: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
-   **regexp**: Regular expression to operate on. Can be a constant, column, or function, and any combination of operators.
-   **start**: Optional start position (the first position is 1) to search for the regular expression. Can be a constant, column, or function.
-   **flags**: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported:
    -   **i**: (insensitive) Ignore case when matching.
    -   **m**: (multi-line) `^` and `$` match the beginning and end of a line, respectively.
    -   **s**: (single-line) `.` matches newline (`\n`).
    -   **R**: (CRLF) When multi-line mode is enabled, `\r\n` is used to delimit lines.
    -   **U**: (ungreedy) Swap the meaning of `x*` and `x*?`.

[](#view-regexp_count-query-example)

View `regexp_count` query example

*The following example uses the [Home sensor sample data](/influxdb3/enterprise/reference/sample-data/#home-sensor-data).*

```sql
SELECT DISTINCT
  room,
  regexp_count(room::STRING, '[Ro]', 1, 'i') AS regexp_count
FROM home
```

| room | regexp_count |
| --- | --- |
| Kitchen | 0 |
| Living Room | 3 |

## regexp\_like

True if a regular expression has at least one match in a string; false otherwise.

```sql
regexp_like(str, regexp[, flags])
```

### Arguments

-   **str**: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
-   **regexp**: Regular expression to test against the string expression. Can be a constant, column, or function.
-   **flags**: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported:
    -   **i**: (insensitive) Ignore case when matching.
    -   **m**: (multi-line) `^` and `$` match the beginning and end of a line, respectively.
    -   **s**: (single-line) `.` matches newline (`\n`).
    -   **R**: (CRLF) When multi-line mode is enabled, `\r\n` is used to delimit lines.
    -   **U**: (ungreedy) Swap the meaning of `x*` and `x*?`.

[](#view-regexp_like-query-example)

View `regexp_like` query example

*The following example uses the [Home sensor sample data](/influxdb3/enterprise/reference/sample-data/#home-sensor-data).*

```sql
SELECT DISTINCT
  room,
  regexp_like(room::STRING, 'R', 'i') AS regexp_like
FROM home
```

| room | regexp_like |
| --- | --- |
| Kitchen | false |
| Living Room | true |

## regexp\_match

Returns a list of regular expression matches in a string.

```sql
regexp_match(str, regexp, flags)
```

### Arguments

-   **str**: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
-   **regexp**: Regular expression to match against. Can be a constant, column, or function.
-   **flags**: Regular expression flags that control the behavior of the regular expression. The following flags are supported.
    -   **i**: (insensitive) Ignore case when matching.

[](#view-regexp_match-query-example)

View `regexp_match` query example

*The following example uses the [Home sensor sample data](/influxdb3/enterprise/reference/sample-data/#home-sensor-data).*

`regexp_match` returns a *list* Arrow type. Use *bracket notation* to reference a value in the list. Lists use 1-based indexing.

```sql
SELECT DISTINCT
  room,
  regexp_match(room::STRING, '.{3}')[1] AS regexp_match
FROM home
```

| room | regexp_match |
| --- | --- |
| Kitchen | Kit |
| Living Room | Liv |

## regexp\_replace

Replaces substrings in a string that match a regular expression.

```sql
regexp_replace(str, regexp, replacement, flags)
```

### Arguments

-   **str**: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
-   **regexp**: Regular expression to match against. Can be a constant, column, or function.
-   **replacement**: Replacement string expression. Can be a constant, column, or function, and any combination of string operators.
-   **flags**: Regular expression flags that control the behavior of the regular expression. The following flags are supported.
    -   **g**: (global) Search globally and don’t return after the first match.
    -   **i**: (insensitive) Ignore case when matching.

[](#view-regexp_replace-query-example)

View `regexp_replace` query example

*The following example uses the [Home sensor sample data](/influxdb3/enterprise/reference/sample-data/#home-sensor-data).*

```sql
SELECT DISTINCT
  room,
  regexp_replace(room::STRING, '\sRoom', '', 'gi') AS regexp_replace
FROM home
```

| room | regexp_replace |
| --- | --- |
| Kitchen | Kitchen |
| Living Room | Living |

[regular expressions](/influxdb3/enterprise/tags/regular-expressions/) [sql](/influxdb3/enterprise/tags/sql/)