REGEXP_SUBSTR Function Syntax | Teradata Vantage - REGEXP_SUBSTR Function Syntax - Analytics Database - Teradata Vantage

SQL Functions, Expressions, and Predicates

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-01-12
dita:mapPath
obm1628111499646.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
kby1472250656485
lifecycle
latest
Product Category
Teradata Vantageā„¢
[TD_SYSFNLIB.] REGEXP_SUBSTR ( source_string, regexp_string
  [, position_arg, occurrence_arg, match_arg ] )

Syntax Elements

TD_SYSFNLIB.
Name of the database where the function is located.
source_string
A character argument.
If source_string is NULL, NULL is returned.
regexp_string
A character argument.
If regexp_string is NULL, NULL is returned.
position_arg
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.
occurrence_arg
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.
match_arg

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.