Redshift Regular Expression Functions and Examples

The Redshift regular expression functions identify precise patterns of characters in the given string and are useful for extracting string from the data and validation of the existing data, for example, validate date, range checks, checks for characters, and extract specific characters from the data. The regular expression functions allow you to perform complex string manipulations within Redshift SQL statements, such as searching for and extracting specific patterns, validating data, and replacing values.

In this article, we will be checking some commonly used Redshift regular expressions with examples.

Read:

Amazon Redshift Extract Numbers using Regular Expressions

Redshift Regular Expression Functions

Below are some of the regular expression function that Amazon redshift supports:

Redshift REGEXP_COUNT Function

This function searches a string for a regular expression pattern 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.

REGEXP_COUNT ( source_string, pattern [, position ] );

training=# select regexp_count('abcadsadsaabcfasdf', '[abc]{3}'); 
 regexp_count 
-------------- 
 2 
(1 row)

Redshift REGEXP_INSTR Function

The REGEXP_INSTR function in Amazon Redshift is used to find the starting position of a regular expression pattern in a string.

Searches a string for a regular expression pattern and returns an integer that indicates the beginning position of the matched substring. If no match is found, then the function returns 0.

REGEXP_INSTR ( source_string, pattern [, position ] );

training=# select regexp_instr('This is the in string example','in string', 1); 
 regexp_instr 
-------------- 
 13 
(1 row)

Redshift REGEXP_REPLACE Function

The REGEXP_REPLACE function in Amazon Redshift is used to replace substrings that match a regular expression pattern in a string.

Searches a string for a regular expression pattern and replaces every occurrence of the pattern with the specified replace_string.

REGEXP_REPLACE ( source_string, pattern [, replace_string [ , position ] ] );

training=# select regexp_replace( 'email_id@gmail.com', '@.*\\.(com)');
regexp_replace
--------------
email_id
(1 row)

Redshift REGEXP_SUBSTR Function

The REGEXP_SUBSTR function in Amazon Redshift is used to extract a substring from a string based on a regular expression pattern.

It returns the characters extracted from a string by searching for a regular expression pattern.

REGEXP_SUBSTR ( source_string, pattern [, position ] );

training=# Select regexp_substr('fruit chocolate chip', 'ch(i|o)p', 1); 
 regexp_substr 
--------------- 
 chip 
(1 row)

Read: