15.00 - REGEXP_SPLIT_TO_TABLE - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

REGEXP_SPLIT_TO_TABLE

Purpose  

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

Syntax  

where:

 

Syntax element …

Specifies …

TD_SYSFNLIB

the 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.

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.

If match_arg is not specified:

  • The match is case sensitive.
  • A period does not match the newline character.
  • source_string is treated as a single line.
  • If there is no match, NULL is returned.

    If match_arg is not valid, an error is returned.

    ANSI Compliance

    This is a Teradata extension to the ANSI SQL:2011 standard.

    Invocation

    REGEXP_SPLIT_TO_TABLE is an embedded services system function. For information on activating and invoking embedded services functions, see “Embedded Services System Functions” on page 24.

    Argument Types and Rules

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

  • source_string = CHAR, VARCHAR
  • regexp_string = CHAR, VARCHAR (maximum size of 512 bytes)
  • match_arg = VARCHAR
  • You can also pass arguments with data types that can be converted to the above types using the implicit data type conversion rules that apply to UDFs.

    Note: The UDF implicit type conversion rules are more restrictive than the implicit type conversion rules normally used by Teradata Database. If an argument cannot be converted to the required data type following the UDF implicit conversion rules, it must be explicitly cast.

    For details, see “Compatible Types” in SQL External Routine Programming.

    Result Type

    The result row type is:

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

    An example of a SELECT statement:

    SELECT * from table(regexp_split_to_table(regexp_split_to_table('phone maker','Apple&Microsoft&Google','&','c')
    returns (outkey varchar(30), token_ndx integer, token varchar(100))) as t1;

    returns a table with the following rows:

    outkey                            token_ndx      	token
    ------------------------------ ----------------  -----------------------
    p h o n e   m a k e r             1     	        A p p l e
    p h o n e   m a k e r             2     	        M i c r o s o f t
    p h o n e   m a k e r             3     	        G o o g l e