# Regular Expression Functions ## Using Regular Expression Commands Suppose you want to find the starting position of a string within another string. Call the former the search string and the latter the target string. To find `dog` within `The dog chased the cat`, you would run the query `SELECT INSTR('The dog chased the cat','dog');`. This query returns `5`, indicating the position where it found `dog`. But [INSTR](https://docs.singlestore.com/cloud/reference/sql-reference/string-functions/instr.md) does not allow search strings that contain placeholders. The following examples show search strings containing placeholders, denoted by the `?` symbol. | Search String | Placeholder | | ---------------------- | --------------------------- | | `The dog chased the ?` | Any string | | `The ? chased the cat` | Any three letter string | | `The dog ? the cat` | The string`chased`or`found` | Regular expressions enable you to define search strings with placeholders. You specify these strings using patterns. For instance, the pattern `The .{3,5} chased the mouse|frog` matches many target strings. Two of them are `The dog chased the frog` and `The rat chased the mouse`. Patterns contain literals and metacharacters. Literals are characters that a pattern matcher attempts to locate in the target string exactly as the characters are written in the search string. Metacharacters describe the placeholders that the pattern uses. The pattern `The .{3,5} chased the mouse|frog` contains the literals `The`,`chased`,`the`,`mouse` and `frog`. It contains the metacharacters `.`,`{3,5}` and `|`. ## Regular Expression Functions SingleStore Helios has six built-in functions that allow you to work with regular expressions. * [`REGEXP`](https://docs.singlestore.com/cloud/reference/sql-reference/regular-expression-functions/rlike-and-regexp.md): Searches a string for a regular expression pattern and returns `1` if the string is found, `0` if the pattern is not found. * [`RLIKE`](https://docs.singlestore.com/cloud/reference/sql-reference/regular-expression-functions/rlike-and-regexp.md): Has the same functionality as `REGEXP`. * [`REGEXP_INSTR`](https://docs.singlestore.com/cloud/reference/sql-reference/regular-expression-functions/regexp-instr.md): Searches a string for a regular expression pattern and returns an integer indicating the beginning position or ending position of the matched substring. * [`REGEXP_SUBSTR`](https://docs.singlestore.com/cloud/reference/sql-reference/regular-expression-functions/regexp-substr.md): Searches a string for a regular expression pattern and returns the matching substring. * [`REGEXP_REPLACE`](https://docs.singlestore.com/cloud/reference/sql-reference/regular-expression-functions/regexp-replace.md): Searches a string for the first occurrence of a regular expression pattern and replaces that occurrence with a replacement string. Can also perform a search and replace of all occurrences. * [`REGEXP_MATCH`](https://docs.singlestore.com/cloud/reference/sql-reference/regular-expression-functions/regexp-match.md): Returns a JSON array of matching substring(s) within the first match of a regular expression pattern to a string. It supports both extended and advanced regular expression formats. You can set the global variable `REGEXP_FORMAT` to specify which format you want to use. ## Regular Expression Format - Advanced Regular Expression (ARE) SingleStore Helios supports advanced regular expressions (ARE) as defined in [PostgreSQL](https://www.postgresql.org/docs/9.3/static/functions-matching.html). ARE are nearly a superset of ERE and support common Perl regular expression extensions. You can use the ARE format with the built-in SingleStore Helios functions `RLIKE`, `REGEXP`,`REGEXP_INSTR`,`REGEXP_SUBSTR`, and `REGEXP_REPLACE`, which are described in the next section. Set the global variable `regexp_format` to specify the regular expression format to be used by the built-in functions you call. To use ARE globally, run: ``` SET GLOBAL regexp_format=advanced; ``` You should set `regexp_format` to `'advanced'` if you are developing new regular expression logic. ## Advanced Regular Expression (ARE) Support SingleStore Helios supports ARE as defined in [PostgreSQL](https://www.postgresql.org/docs/9.3/static/functions-matching.html). This support is summarized below. ## Backreferences A pattern containing a backreference matches the same string that it matched earlier in the string. The earlier string must be enclosed in parentheses. A pattern may contain multiple backreferences that are denoted by `\\n`, where `n` is the number of the backreference. For example, the pattern `a(bc)d(ef)g\\1\\2` matches `abcdefgbcef`. ## Escapes Escapes make it easier to write some types of patterns. Common escapes are listed below. See section 9.7.3.3 of the [PostgreSQL pattern matching documentation](https://www.postgresql.org/docs/9.3/static/functions-matching.html) for a full explanation of the supported escapes. | Metacharacter | Meaning | | ------------- | ----------------------------------------------------------------------- | | `\d` | Matches any digit. | | `\s` | Matches a space. | | `\w` | Matches any alpha-numeric character including the underscore. | | `\D` | Matches any character except a digit. | | `\S` | Matches any character except a space. | | `\W` | Matches any non-alpha-numeric character. Does not match the underscore. | | `\m` | Matches the beginning of a word. | | `\M` | Matches the end of a word. | > **📝 Note**: In PostgreSQL, escapes in patterns begin with one `\` character.In SingleStore Helios, escapes in patterns also begin with one `\` character. Additionally, a `\` literal in an SingleStore Helios string needs to be escaped with a leading `\`. Hence, pattern strings containing escapes use two `\` characters. For example, `SELECT REGEXP_INSTR('dog58','\\d');` ## Error Handling Error handling for regular expression functions is controlled by the `regexp_error_handling_level` engine variable. The following table shows the allowed values and the error handling behavior. | Value | Behavior | | ----- | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 8.7 | Preserves prior behavior. NULL is returned when the memory limit is reached and when the pattern is not a string literal. Provided for backward compatibility. | | 8.9 | Returns an error when the memory limit is reached. Returns NULL on other errorswhen the pattern is not a string literal. | | 9.0 | Returns an error when the memory limit is reached and when the pattern is not a string literal. | ## In this section * [REGEXP\_INSTR](https://docs.singlestore.com/cloud/reference/sql-reference/regular-expression-functions/regexp-instr.md) * [REGEXP\_MATCH()](https://docs.singlestore.com/cloud/reference/sql-reference/regular-expression-functions/regexp-match.md) * [REGEXP\_REPLACE](https://docs.singlestore.com/cloud/reference/sql-reference/regular-expression-functions/regexp-replace.md) * [REGEXP\_SUBSTR](https://docs.singlestore.com/cloud/reference/sql-reference/regular-expression-functions/regexp-substr.md) * [RLIKE and REGEXP](https://docs.singlestore.com/cloud/reference/sql-reference/regular-expression-functions/rlike-and-regexp.md) *** Modified at: September 25, 2025 Source: [/cloud/reference/sql-reference/regular-expression-functions/](https://docs.singlestore.com/cloud/reference/sql-reference/regular-expression-functions/) (An index of the documentation is available at /llms.txt)