REGEXP_SUBSTR function - Amazon Redshift


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.


REGEXP_SUBSTR ( source_string, pattern [, position [, occurrence [, parameters ] ] ] )



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


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


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 ("").


A positive integer that indicates which occurrence of the pattern to use. REGEXP_SUBSTR skips the first occurrence -1 matches. The default is 1. If occurrence is less than 1 or greater than the number of characters in source_string, the search is ignored and the result is NULL.


One or more string literals that indicate how the function matches the pattern. The possible values are the following:

  • c – Perform case-sensitive matching. The default is to use case-sensitive matching.

  • i – Perform case-insensitive matching.

  • e – Extract a substring using a subexpression.

    If pattern includes a subexpression, REGEXP_SUBSTR matches a substring using the first subexpression in pattern. REGEXP_SUBSTR considers only the first subexpression; additional subexpressions are ignored. If the pattern doesn't have a subexpression, REGEXP_SUBSTR ignores the 'e' parameter.

Return type



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

select email, regexp_substr(email,'@[^.]*') from users limit 5; email | regexp_substr --------------------------------------------+---------------- | @nonnisiAenean | @lacusUtnec | @semperpretiumneque | @tristiquealiquet | @sodalesat