SQL regular expression functions

The InfluxDB Cloud Serverless SQL implementation uses the PCRE-like regular expression syntax (excluding some features such as look-around and back-references) and supports the following regular expression functions:

• regexp_count
• regexp_like
• regexp_match
• regexp_replace

regexp_count

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

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*?.

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

regexp_like

True if a regular expression 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: (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*?.

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

regexp_match

Returns a list of regular expression 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: Regular expression flags that control the behavior of the regular expression. The following flags are supported.
  • i: (insensitive) Ignore case when matching.

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

regexp_replace

Replaces substrings in a string that match a regular expression.

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.

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