SQL regular expression functions

The InfluxDB SQL implementation uses the POSIX regular expression syntax and supports the following regular expression functions:

• regexp_match
• regexp_replace

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