Regular expression functions

Match text by pattern instead of by position.

Syntax

Write the function's name, the text and the pattern.

REGEX_TEST(text, pattern)
REGEX_REPLACE(text, pattern, replacement)
REGEXREPLACE(text, pattern, replacement)
SPLIT_STR(text, separator)

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • REGEX_TEST

    text

    Required: Yes
  • pattern

    Required: Yes
  • REGEX_REPLACE

    text

    Required: Yes
  • pattern

    Required: Yes
  • replacement

    Required: Yes
  • REGEXREPLACE

    text

    Required: Yes
  • pattern

    Required: Yes
  • replacement

    Required: Yes
  • SPLIT_STR

    text

    Required: Yes
  • separator

    Required: Yes

Examples

A product code must be three capitals, a hyphen and four digits; two candidates are tested, and a word is tested with the case ignored.

Return type and behaviour

Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.

FunctionReturnsBehaviourForm
REGEX_TESTtrue or falseTrue when the pattern matches somewhere in the textREGEX_TEST("abc123", "[0-9]+")
REGEX_REPLACEtextThe text with the pattern replacedREGEX_REPLACE("abc123", "[0-9]+", "#")
REGEXREPLACEtextThe text with every match of the pattern replaced. It does the same job as REGEX_REPLACEREGEXREPLACE("a1b22", "[0-9]+", "#")
SPLIT_STRlist of textThe piecesSPLIT_STR("a,b,c", ",")
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-5004An argument is missingGive the text, the pattern and, for REGEX_REPLACE, the replacement
HTQL-2001The pattern is not a valid regular expression, such as an unclosed bracket. The message shows whereCorrect the pattern
Limitations
  • Quote the pattern as text. A backslash may be written once or twice: "\\d" and "\d" are the same pattern.
  • A pattern is Rust regular expression syntax. It has no look-ahead and no back-references.
  • Coming: a negated class written as exactly [^0-9] is read as [0-9]: REGEX_REPLACE("a1b", "[^0-9]", "#") answers "a#b", where "#1#" is meant, and REGEX_TEST("123", "[^0-9]") answers true. Other negated classes, such as [^a-z] and [^0-9]+, are read correctly. Use \D for a non-digit.
Permissions and security

These functions read nothing from your tables and write nothing.

When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.

Best practices
  • Test a pattern with REGEX_TEST before you use it to replace.
  • Anchor a pattern with ^ and $ to match the whole value.