REGEXP_SPLIT_TO_TABLE IN TERADATA

Share via:
REGEXP_SPLIT_TO_TABLE IN TERADATA

Dear Readers,

In this article, we will learn about REGEXP_SPLIT_TO_TABLE IN TERADATA

Purpose

Splits source_string into a table of strings using regexp_string as the delimiter.

Syntax:

TD_SYSFNLIB.

Name of the database where the function is located.

inkey

A numeric or character argument that uniquely identifies the source_string in the output result set.

If the inkey is NULL, an empty string is returned.

source_string

A character argument.

If source_string is NULL, 0 rows are returned. Zero rows are also returned if the source_string is the empty string.

regexp_string

A character argument.

If regexp_string is NULL, the original source_string is returned.

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. Although the maximum source_string size is 16 MB, the resulting token can only be VARCHAR, and has a maximum return token size of 64000 bytes.

If match_arg is not specified, an error is returned.

If there is no match, the original source_string is returned.

Outkey

The value of inkey.

token_ndx

The ordinal position of the token in the input string.

token

A character argument.

The token from the input string in the same character set as instring.

Argument Types and Rules

Expressions passed to this function must have the following data types:

  • inkey = NUMERIC, VARCHAR
  • source_string = CHAR, VARCHAR
  • regexp_string = CHAR, VARCHAR
  • match_arg = VARCHAR

The function returns an error if this size is exceeded or if match_arg = ‘l’ is specified.

If the inkey is null, an empty string is returned.

The x match option ignores whitespace characters in the pattern/regexp_string. By default, whitespace characters match themselves.

If Teradata Database passes constants as the second and third parameter in an OREPLACE call, the character type of the first argument is passed as Unicode, and calls oreplace_unicode() with the return type VARCHAR in Unicode charset.

Result Type

The result row type is:

  • outkey = NUMERIC, VARCHAR
  • token_ndx = INTEGER
  • token = VARCHAR

Example

An example of a SELECT statement:

returns a table with the following rows:

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTexperts
Linkedin Page : KT EXPERTS

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment