REGEXP
Checks if the source string matches the regular expression pattern provided.
See also:
[SELECT]
Syntax
SELECT source_string REGEXP pattern_expression AS match_result FROM <multipartIdentifier>;
Arguments
source_string
The string in which to search for the pattern.
pattern_expression
The regular expression pattern to match against the source string.
Return Value(s)
Returns 1 if the source string matches the regular expression pattern, otherwise 0.
Examples
Check if a string contains numbers
This example demonstrates checking whether the string 'the year is 2021' contains one or more numeric characters:
SELECT 'the year is 2021' REGEXP '[0-9]+' AS match_result;
This query will return 1 because the string contains the substring '2021', which matches the pattern [0-9]+, indicating one or more digits.
Validate email format
To check if a string is in a valid email format:
SELECT 'user@example.com' REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$' AS match_result;
This query returns 1, indicating that 'user@example.com' is formatted like a typical email address, matching the specified regular expression pattern. The dot before the domain suffix is written \\. because a StackQL string literal consumes one backslash, so a backslash that belongs to the regular expression is written twice (see Regex Reference).