Categories: String, Regular Expressions

REGEXP_MATCHES

Returns whether a string matches a regular expression.

Syntax

REGEXP_MATCHES(input string, regex string) → boolean

• input: The string to test.
• regex: The Perl-compatible regular expression (PCRE) to use for the test. Must be a literal.

Examples

REGEXP_MATCHES example

SELECT REGEXP_MATCHES('the data lakehouse', '.*?\Qlake\E.*?')
-- True

REGEXP_MATCHES example

SELECT Category, REGEXP_MATCHES(Category, '.*?\Q-\E.*?') 
FROM Samples."samples.dremio.com"."SF_incidents2016.json" 
LIMIT 5
-- Category, EXPR$1
-- WEAPON LAWS, false
-- WEAPON LAWS, false
-- WARRANTS, false
-- NON-CRIMINAL, true
-- NON-CRIMINAL, true

REGEXP_MATCHES example

SELECT s_manager 
FROM Samples."samples.dremio.com"."tpcds_sf1000".store 
WHERE REGEXP_MATCHES(s_manager, '.*?\Qoo\E.*?') 
GROUP BY s_manager
-- Brian Cooke
-- Richard Brooks
-- William Moody
-- Tony Cook
-- Joseph Crook
-- Ricky Cooper
-- Tom Brooks
-- Barry Booker
-- Alfred Norwood
-- Grady Moore
-- Theo Wood
-- Walter Hood
-- Vince Moore
-- Robert Moore
-- Cedric Cooper
-- Hugh Wood
-- Jorge Dooley
-- David Wood
-- Joseph Moore
-- Kim Bloom
-- Mario Cook
-- Peter Woodward
-- Jason Goode
-- Nolan Wood
-- William Coons
-- Patrick Smoot
-- John Moody
-- Jerry Brooks
-- Jeffery Good
-- Dominique Cook
-- Ray Moore
-- Brandon Moore
-- Luis Wood

Usage Notes

This function is identical to the function REGEXP_LIKE.