Determines if a string matches a regular expression.


(string) Regular expression in ECMA262 format.

(string) Input string.

Return values

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

// 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";

Uses ECMA262 format for regular expressions:

REGEXP can be used as a both a function, and also as an operator, as explained in

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

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);