Snowflake Regular Expression Functions

 The Snowflake regular expression functions identify the precise pattern of the characters in given string. Regular expressions are commonly used in validating strings.

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

·         REGEXP_COUNT

·         REGEXP_INSTR

·         REGEXP_LIKE

·         REGEXP_SUBSTR

·         REGEXP_REPLACE

REGEXP_COUNT ():

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.

Syntax:

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

Ex:

Below query to search for pattern ‘abc’.

Select regexp_count(‘qqqabcrtrababcbcd’, ‘[abc]{3}’) as result;

O/P:

RESULT – 3

REGEXP_INSTR ():

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.

Syntax:

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

Ex:

Below query to search for pattern ‘abc’.

Select regexp_count (‘qqqabcrtrababcbcd’, ‘abc’) as result;

O/P:

RESULT – 2

REGEXP_LIKE ():

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

Syntax:

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

Ex:

Select Ename from emp where regexp_like (Ename, ‘S.*);

O/P:

Ename

-----------

 Smith

 Scott

REGEXP_SUBSTR ():

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.

Syntax:

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

Ex:

Select regexp_substr(‘Fruit chocolate chip’ , ‘ch(i|o)p’, 1) as Result;

O/P:

Result

------------

Chip

REGEXP_REPLACE ():

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

Syntax:

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

Ex:

Select regexp_replace( ‘ABC@gmail.com’ , ‘@.*\\.(com)’) as result;

O/P:

Result ------ ABC