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
Required: Yestext
Required: YespatternREGEX_REPLACE
Required: Yestext
Required: Yespattern
Required: YesreplacementREGEXREPLACE
Required: Yestext
Required: Yespattern
Required: YesreplacementSPLIT_STR
Required: Yestext
Required: Yesseparator
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
REGEX_TEST | true or false | True when the pattern matches somewhere in the text | REGEX_TEST("abc123", "[0-9]+") |
REGEX_REPLACE | text | The text with the pattern replaced | REGEX_REPLACE("abc123", "[0-9]+", "#") |
REGEXREPLACE | text | The text with every match of the pattern replaced. It does the same job as REGEX_REPLACE | REGEXREPLACE("a1b22", "[0-9]+", "#") |
SPLIT_STR | list of text | The pieces | SPLIT_STR("a,b,c", ",") |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-5004 | An argument is missing | Give the text, the pattern and, for REGEX_REPLACE, the replacement |
HTQL-2001 | The pattern is not a valid regular expression, such as an unclosed bracket. The message shows where | Correct 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, andREGEX_TEST("123", "[^0-9]")answers true. Other negated classes, such as[^a-z]and[^0-9]+, are read correctly. Use\Dfor 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.