Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

REGEXP_SUBSTR Function

Returns the characters extracted from a string by searching for a regular expression pattern. REGEXP_SUBSTR is similar to the SUBSTRING Function function, but lets you search a string for a regular expression pattern. For more information about regular expressions, see POSIX Operators.

Syntax

Copy
REGEXP_SUBSTR ( source_string, pattern [, position ] )

Arguments

source_string

A string expression, such as a column name, to be searched.

pattern

A string literal that represents a SQL standard regular expression pattern.

position

A positive integer that indicates the position within source_string to begin searching. The position is based on the number of characters, not bytes, so that multi-byte characters are counted as single characters. The default is 1. If position is less than 1, the search begins at the first character of source_string. If position is greater than the number of characters in source_string, the result is an empty string ("").

Return Type

VARCHAR

Example

The following example returns the portion of an email address between the @ character and the domain extension.

Copy
select email, regexp_substr(email,'@[^.]*') from users limit 5; email | regexp_substr --------------------------------------------+---------------- Suspendisse.tristique@nonnisiAenean.edu | @nonnisiAenean sed@lacusUtnec.ca | @lacusUtnec elementum@semperpretiumneque.ca | @semperpretiumneque Integer.mollis.Integer@tristiquealiquet.org | @tristiquealiquet Donec.fringilla@sodalesat.org | @sodalesat