Regular Expression Functions

Perform pattern matching and text manipulation using regex.

REGEXP_CONTAINS( <expr>, <regexp_pattern>)

Returns TRUE if the value is a partial match for the regular expression.

Supported data types, string and integer

> SELECT REGEXP_CONTAINS('abc', '[a-z]{3}');
TRUE

REGEXP_REPLACE( <expr>, <regexp_pattern> , <replacement> )

Returns a STRING where all substrings of value that match regular expression regexp are replaced with replacement. replacement is optional, by default it is empty.

Supported data types, string and integer

> SELECT REGEXP_REPLACE('Customers - (NY)','\(|\)','');
Customers-NY

REGEXP_EXTRACT( <expr>, <regexp_pattern> )

Returns the first string from the string <expr> that matches <regex pattern>.

Supported data types, string and integer

> SELECT regexp_extract('1a 2b 14m', '\d+')
 1

REGEXP_EXTRACT_ALL( <expr>, <regexp_pattern> )

Returns an array of all substrings that match the regexp. Returns an empty array if there is no match.

Supported data types, string and integer

> select REGEXP_EXTRACT_ALL('a1_a2a3_a4A5a6', 'a[1-9]');
[a1, a2, a3, a4, a6]

REGEXP_COUNT ( <expr>, <regexp_pattern> )

Returns the number of times that a regexp pattern occurs in a string.

Supported data types, string and integer

> select regexp_count('It was the best of times, it was the worst of times', '\bwas\b') as "result";
2 

REGEXP_LIKE ( <expr>, <regexp_pattern> )

Evaluates the regular expression pattern and check if it is contained within string. It returns True if regexp pattern is present in the expression.

Supported data types, string and integer

> select regexp_like('E6data','[a-zA-Z0-9]{6}');
True

> select regexp_like('123456','[0-9]{7}');
False

Note -

Regular expression functions use the Java pattern

Right now, we have tested on character class and pre-defined character class

Limitations -

• Complex types such array, struct or map are not supported for querying with regular expressions