15.00 - ARRAY Concatenation Function - 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)
Last Update
2018-09-24

ARRAY Concatenation Function

Purpose  

Concatenates one-dimensional ARRAY data types and can be applied to a subset of the elements of the array.

Syntax  

System function syntax:

Method-style syntax:

where:

 

Syntax element…

Specifies…

expr1

 

An ARRAY expression, which is one of the following:

  • A reference to an ARRAY column
  • An ARRAY constructor expression. See “ARRAY Constructor Expression” on page 195.
  • A UDF expression
  • A UDM expression
  • expr2

    An ARRAY expression, which is one of the following:

  • A reference to an ARRAY column
  • An ARRAY constructor expression. See “ARRAY Constructor Expression” on page 195.
  • A UDF expression
  • A UDM expression
  • scope_reference

    an optional ARRAY scope reference. See “ARRAY Scope Reference” on page 198.

    ANSI Compliance

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

    Argument Type

    expr1 and expr2 must be ARRAY expressions, and they must have the same 1-D ARRAY data type as defined by a CREATE TYPE statement.

    Result Type and Value

    The result of the concatenation operation is a new 1-D ARRAY value of the same type as the data type of the two arguments, where all of the elements present in expr1 are followed by all the elements that are present in expr2. Elements that are present include NULL elements, but do not include elements that are in an uninitialized state.

    Since the result value and the argument values are of the same 1-D ARRAY type, they have the same maximum size n. Therefore, if the number of elements that results from the concatenation of expr1 and expr2 is greater than the maximum size n of the defined 1-D ARRAY type, the operation will abort with an error.

    If either argument is NULL, the result of the operation is NULL.

    Restrictions

    The ARRAY concatenation function cannot be used with multidimensional ARRAY data types.

    Example

    This example takes two 1-D ARRAY values of the same data type and concatenates them together. PhoneNum and OldPhoneNum are columns in the employee_info table, and both columns have the same 1-D ARRAY data type.

    /* The following select statement concatenates the current value in "PhoneNum" with the value in "OldPhoneNum". */
     
    /* Assume one row in table employee_info contains the following value for "PhoneNum": 
    PhoneNum[1] = '6197211000'
    PhoneNum[2] = '6197221000'
    PhoneNum[3] = '6197231000'
     
    Also, assume one row in table employee_info contains the following value for "OldPhoneNum": 
    OldPhoneNum[1] = '8582001000'
    OldPhoneNum[2] = '8582002000'
    OldPhoneNum[3] = '8582003000'
    */
     
    SELECT ARRAY_CONCAT(PhoneNum, OldPhoneNum)
    FROM employee_info;
     
    /* Result value is the following:
    PhoneNum[1] = '6197211000'
    PhoneNum[2] = '6197221000'
    PhoneNum[3] = '6197231000'
    PhoneNum[4] = '8582001000'
    PhoneNum[5] = '8582002000'
    PhoneNum[6] = '8582003000'
    */

    The following shows the same query using method-style syntax:

    SELECT PhoneNum.ARRAY_CONCAT(OldPhoneNum)
    FROM employee_info;