15.00 - ARRAY Concatenation Operator - 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 Operator

Purpose  

Concatenates one-dimensional ARRAY data types.

Syntax  

where:

 

Syntax element…

Specifies…

array_expr1

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
  • array_expr2

    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
  • ||

    the concatenation operator

    ANSI Compliance

    The ARRAY concatenation operator for one-dimensional (1-D) ARRAY data types is partially ANSI SQL:2011 compliant.

    The ARRAY concatenation operator requires that both operands are instances of the same ARRAY type and that the target type of the concatenation operation is also the same type. This is a deviation from the ANSI standard because it defines the target data type of a 1-D ARRAY concatenation to be a new 1-D ARRAY type with the length defined as the sum of the length of both operands.

    Argument Type

    array_expr1 and array_expr2 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 array_expr1 are followed by all the elements that are present in array_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 array_expr1 and array_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 operator 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.

    CREATE TYPE address AS CHAR(10) ARRAY[5];
     
    CREATE TABLE employee_info (eno INTEGER, addressval address);
     
    /* Assume one row in table employee_info contains the following value 
    for "addressval": 
    Addressval[1] = '123 Main St.'
    Addressval[2] = 'San Diego'
    Addressval[3] = 'CA'
    */
     
    /* The following select statement concatenates the current value in "addressval" with ZIP code and country information, which are stored as additional elements of the 1-D array. */
     
    SELECT addressval || NEW address('92101', 'USA');
    FROM employee_info;
     
    /* Result value is the following:
    Addressval[1] = '123 Main St.'
    Addressval[2] = 'San Diego'
    Addressval[3] = 'CA'
    Addressval[4] = '92101'
    Addressval[5] = 'USA'
    */