REGEXP_SUBSTR Function Syntax | Teradata Vantage - 17.10 - REGEXP_SUBSTR Function Syntax - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Functions, Expressions, and Predicates

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
[TD_SYSFNLIB.] REGEXP_SUBSTR ( source_string, regexp_string
  [, position_arg, occurrence_arg, match_arg ] )

Syntax Elements

Name of the database where the function is located.
A character argument.
If source_string is NULL, NULL is returned.
A character argument.
If regexp_string is NULL, NULL is returned.
A numeric argument.
position_arg specifies the position in source_string from which to start searching (default is 1).
If position_arg is greater than the input string length, NULL is returned.
If position_arg is NULL, the value NULL is used. If position_arg is not specified, the default (1) is used.
A numeric argument.
Specifies the number of the occurrence to be returned (default is 1). For example, if occurrence_arg is 2, the function matches the first occurrence in source_string and starts searching from the character following the first occurrence in source_string for the second occurrence in source_string.
If occurrence_arg is greater than the number of matches found, NULL is returned.
If occurrence_arg is NULL, a NULL result is returned. If occurrence_arg is omitted, the default value (1) is used.

A character argument.

Valid values are:
  • 'i' = case-insensitive matching.
  • 'c' = case sensitive matching.
  • 'n' = the period character (match any character) can match the newline character.
  • 'm' = source_string is treated as multiple lines instead of as a single line. With this option, the '^' and '$' characters apply to each line in source_string instead of the entire source_string.
  • 'l' = if source_string exceeds the current maximum allowed source_string size (currently 16 MB), a NULL is returned instead of an error. This is useful for long-running queries where you do not want long strings causing an error that would make the query fail.
  • 'x' = ignore whitespace.
The argument can contain more than one character. If a character in the argument is not valid, then that character is ignored.
If match_arg is not specified, is NULL, or is empty:
  • The match is case-sensitive.
  • A period does not match the newline character.
  • source_string is treated as a single line.