REGEXP_REPLACE

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

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

Purpose

Replaces portions of source_string that match regexp_string with the replace_string.

REGEXP_REPLACE supports 2, 3, 4, 5, or 6 parameters.

Syntax



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.
replace_string
A character argument.
If a replace_string is not specified, is NULL or is an empty string, the matches are removed from the result.
The maximum backreference number in a replace_string is 9 (for example, \9). Any backreference in the replace_string that is higher than 9 is considered a backreference.
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 occurrence to replace the match with replace_string.
  • If a value of 0 is specified, all occurrences are replaced.
  • If the value is greater than 1, the search begins for the second occurrence beginning with the first character following the first occurrence of the regexp_string, and so on.
If occurrence_arg is greater than the number of matches found, nothing is replaced and source_string is returned.
If occurrence_arg is NULL, a NULL result is returned. If occurrence_arg is omitted, 0 is the default value.
match_arg

A character argument.

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.
  • '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' = For a CLOB data type, 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. You can only specify this option for a CLOB data type.
  • 'x' = ignore whitespace.

REGEXP_REPLACE supports 2, 3, 4, 5, or 6 parameters.

Limitation: NULL inside Input Strings

REGEXP_REPLACE has a limitation in handling ASCII Chr(0), which is NULL. If you concatenate two strings using ASCII Chr(0), REGEXP_REPLACE cannot handle that input string. For example, consider the following code:
sel TD_SYSFNLIB.RegExp_Replace ('a'||chr(0)||'bc', '[b]', 'X', 1, 0) AS 
regex_rep_input_string;
The function is supposed to replace the b in the input string 'a'||chr(0)||'bc' with X. The result should be a Xc. However, because of the limitation, the result is a.