Skip to main content

1E 9.x (on-premises)

REGEXP

Function

REGEXP

Action

Determines if a string matches a regular expression.

Parameters

(string) Regular expression in ECMA262 format.

(string) Input string.

Return values

(integer) Match is true (1) or false (0).

Examples

// Example data
@testTable = SELECT "This will pass the test" AS Data
UNION SELECT "This will not pass the test."
UNION SELECT "this won't pass the test";

// This example uses the REGEXP function to identify which strings in the test table match and do not match
SELECT Data, REGEXP("This .* test", Data) AS [Match] FROM @testTable;
 
// This example uses the REGEXP operator to return only matching strings
SELECT Data FROM @testTable WHERE Data REGEXP "This .* test";

Notes

REGEXP can be used as a both a function, and also as an operator, as explained in www.sqlite.org/lang_expr.html#regexp.

// Function
REGEXP("<regular expression>", "<input string>")
 
// Infix operator
"<input string>" REGEXP "<regular expression>"

Note

Backslashes inside the REGEXP expression will need additional escaping:

@test = select "c:\\users\\potato\\documents\\business" as Path;
// Note the additional escaping below inside the REGEXP(expr,data):
select * from @test where REGEXP("c:\\\\users\\\\.*\\\\documents\\\\business", Path);