Snowflake Regular Expression Functions and Examples

The regular expressions are commonly used functions in programming languages such as Python, Java, R, etc. The Snowflake regular expression functions identify the precise pattern of the characters in given string. Regular expressions are commonly used in validating strings, for example, extracting numbers from the string values, etc.

In this article, we will check the supported Regular expression functions in Snowflake.

Snowflake Regular Expression Functions

The regular expression functions are string functions that match a given regular expression. These functions are commonly called as a ‘regex’ functions.

Below are some of the regular expression function that Snowflake cloud data warehouse supports:

REGEXP_COUNT
REGEXP_INSTR
REGEXP_LIKE
REGEXP_SUBSTR
REGEXP_REPLACE
REGEXP
RLIKE

Now let us check these functions in brief.

Snowflake REGEXP_COUNT Function

The REGEXP_COUNT function searches a string and returns an integer that indicates the number of times the pattern occurs in the string. If no match is found, then the function returns 0.

Following is the syntax of the Regexp_count function.

REGEXP_COUNT( <string> , <pattern> [ , <position> , <parameters> ] )

For example, consider below query to search for pattern ‘abc‘.

select regexp_count('qqqabcrtrababcbcd', '[abc]{3}');

+-----------------------------------------------+
| REGEXP_COUNT('QQQABCRTRABABCBCD', '[ABC]{3}') |
|-----------------------------------------------|
|                                             3 |
+-----------------------------------------------+

Snowflake REGEXP_INSTR Function

The Snowflake REGEXP_INSTR string function returns the position of the specified occurrence of the regular expression pattern in the string. If no match is found, returns 0.

Following is the syntax of the regexp_instr function.

REGEXP_INSTR( <string> , <pattern> [ , <position> [ , <occurrence> [ , <option> [ , <regexp_parameters> [ , <group_num> ] ] ] ] ] )

For example, consider below query to search for pattern ‘abc‘.

select regexp_count('qqqabcrtrababcbcd', 'abc');

+------------------------------------------+
| REGEXP_COUNT('QQQABCRTRABABCBCD', 'ABC') |
|------------------------------------------|
|                                        2 |
+------------------------------------------+

Snowflake REGEXP_LIKE Function

The Snowflake REGEXP_LIKE function returns true if the string matches the pattern.

Following is the syntax of the Regexp_like function.

REGEXP_LIKE( <string> , <pattern> [ , <parameters> ] )

For example, consider following query to match string using regex.

SELECT * 
FROM   ( 
              SELECT 'Bangalore' AS city 
              UNION ALL 
              SELECT 'Mangalore' AS city ) AS tmp 
WHERE  regexp_like (city, 'M.*');

+-----------+
| CITY      |
|-----------|
| Mangalore |
+-----------+

Snowflake REGEXP_SUBSTR Function

The Snowflake REGEXP_SUBSTR function returns the characters extracted from a string by searching for a regular expression pattern. If no match is found, returns NULL.

Following is the syntax of the Regexp_substr function.

REGEXP_SUBSTR( <string> , <pattern> [ , <position> [ , <occurrence> [ , <regex_parameters> [ , <group_num ] ] ] ] )

For example, consider following query to return matching string using regex.

Select REGEXP_SUBSTR('fruit chocolate chip', 'ch(i|o)p', 1);

+------------------------------------------------------+
| REGEXP_SUBSTR('FRUIT CHOCOLATE CHIP', 'CH(I|O)P', 1) |
|------------------------------------------------------|
| chip                                                 |
+------------------------------------------------------+

Snowflake REGEXP_REPLACE Function

The Snowflake REGEXP_REPLACE function returns the string by replacing specified pattern. If no matches found, original string will be returned.

Following is the syntax of the Regexp_replace function.

REGEXP_REPLACE( <string> , <pattern> [ , <replacement> , <position> , <occurrence> , <parameters> ] )

For example, consider following query to return only user name.

select regexp_replace( 'email_id@gmail.com', '@.*\\.(com)');

+------------------------------------------------------+
| REGEXP_REPLACE( 'EMAIL_ID@GMAIL.COM', '@.*\\.(COM)') |
|------------------------------------------------------|
| email_id                                             |
+------------------------------------------------------+

Snowflake REGEXP Function

The Snowflake REGEXP function is an alias for RLIKE.

Following is the syntax of the REGEXP function.

-- 1st syntax
REGEXP( <string> , <pattern> [ , <parameters> ] )

-- 2nd syntax
<string> REGEXP <pattern>

For example, consider following query to matches string with query.

SELECT city REGEXP 'M.*' 
FROM   ( 
              SELECT 'Bangalore' AS city 
              UNION ALL 
              SELECT 'Mangalore' AS city ) AS tmp;

+-------------------+
| CITY REGEXP 'M.*' |
|-------------------|
| False             |
| True              |
+-------------------+

Snowflake RLIKE Function

The Snowflake RLIKE function is an alias for REGEXP and regexp_like.

Following is the syntax of the RLIKE function.

-- 1st syntax
RLIKE( <string> , <pattern> [ , <parameters> ] )

-- 2nd syntax
<string> RLIKE <pattern>

For example, consider following query to matches string with query.

SELECT city RLIKE 'M.*' 
FROM   ( 
              SELECT 'Bangalore' AS city 
              UNION ALL 
              SELECT 'Mangalore' AS city ) AS tmp;

+------------------+
| CITY RLIKE 'M.*' |
|------------------|
| False            |
| True             |
+------------------+

Hope this helps 🙂