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

# Regular expressions

*The Ingest Engine scalar functions are based on* [*Apache DataFusion*](https://arrow.apache.org/datafusion/) *and these docs are derived from the DataFusion function reference.*

Ingest Engine uses a [PCRE-like](https://en.wikibooks.org/wiki/Regular_Expressions/Perl-Compatible_Regular_Expressions) regular expression [syntax](https://docs.rs/regex/latest/regex/#syntax) (minus support for several features including look-around and backreferences).

| Function | Description | Example | Result |
| - | - | - | - |
| `regexp_like(str, pattern [, flags])` | Test if pattern matches | `regexp_like('hello123', '[0-9]+')` | `true` |
| `regexp_match(str, pattern [, flags])` | Return matches | `regexp_match('hello123', '([0-9]+)')` | `[123]` |
| `regexp_replace(str, pattern, repl [, flags])` | Replace matches | `regexp_replace('hello123', '[0-9]+', 'NUM')` | `'helloNUM'` |
| `position(substr IN str)` | Position of substring | `position('lo' IN 'hello')` | `4` |

## `regexp_like`

Returns true if a [regular expression](https://docs.rs/regex/latest/regex/#syntax) has at least one match in a string, false otherwise.

```
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**: case-insensitive: letters match both upper and lower case
  * **m**: multi-line mode: ^ and \$ match begin/end of line
  * **s**: allow . to match \n
  * **R**: enables CRLF mode: when multi-line mode is enabled, \r\n is used
  * **U**: swap the meaning of x\* and x\*?

**Example**

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
select regexp_like('Köln', '[a-zA-Z]ö[a-zA-Z]{2}');
+--------------------------------------------------------+
| regexp_like(Utf8("Köln"),Utf8("[a-zA-Z]ö[a-zA-Z]{2}")) |
+--------------------------------------------------------+
| true                                                   |
+--------------------------------------------------------+
SELECT regexp_like('aBc', '(b|d)', 'i');
+--------------------------------------------------+
| regexp_like(Utf8("aBc"),Utf8("(b|d)"),Utf8("i")) |
+--------------------------------------------------+
| true                                             |
+--------------------------------------------------+
```

## `regexp_match`

Returns a list of [regular expression](https://docs.rs/regex/latest/regex/#syntax) matches in a string.

```
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**: Optional regular expression flags that control the behavior of the regular expression. The following flags are supported:
  * **i**: case-insensitive: letters match both upper and lower case
  * **m**: multi-line mode: ^ and \$ match begin/end of line
  * **s**: allow . to match \n
  * **R**: enables CRLF mode: when multi-line mode is enabled, \r\n is used
  * **U**: swap the meaning of x\* and x\*?

**Example**

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
select regexp_match('Köln', '[a-zA-Z]ö[a-zA-Z]{2}');
+---------------------------------------------------------+
| regexp_match(Utf8("Köln"),Utf8("[a-zA-Z]ö[a-zA-Z]{2}")) |
+---------------------------------------------------------+
| [Köln]                                                  |
+---------------------------------------------------------+
SELECT regexp_match('aBc', '(b|d)', 'i');
+---------------------------------------------------+
| regexp_match(Utf8("aBc"),Utf8("(b|d)"),Utf8("i")) |
+---------------------------------------------------+
| [B]                                               |
+---------------------------------------------------+
```

## `regexp_replace`

Replaces substrings in a string that match a [regular expression](https://docs.rs/regex/latest/regex/#syntax).

```
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**: Optional 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**: case-insensitive: letters match both upper and lower case
  * **m**: multi-line mode: ^ and \$ match begin/end of line
  * **s**: allow . to match \n
  * **R**: enables CRLF mode: when multi-line mode is enabled, \r\n is used
  * **U**: swap the meaning of x\* and x\*?

**Example**

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT regexp_replace('foobarbaz', 'b(..)', 'X\\1Y', 'g');
+------------------------------------------------------------------------+
| regexp_replace(Utf8("foobarbaz"),Utf8("b(..)"),Utf8("X\1Y"),Utf8("g")) |
+------------------------------------------------------------------------+
| fooXarYXazY                                                            |
+------------------------------------------------------------------------+
SELECT regexp_replace('aBc', '(b|d)', 'Ab\\1a', 'i');
+-------------------------------------------------------------------+
| regexp_replace(Utf8("aBc"),Utf8("(b|d)"),Utf8("Ab\1a"),Utf8("i")) |
+-------------------------------------------------------------------+
| aAbBac                                                            |
+-------------------------------------------------------------------+
```

## `position`

Returns the position of `substr` in `origstr` (counting from 1). If `substr` does not appear in `origstr`, return 0.

```
position(substr in origstr)
```

**Arguments**

* **substr**: The pattern string.
* **origstr**: The model string.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.