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

Concatenation Operator

Purpose  

Concatenates string expressions.

Syntax  

where:

 

Syntax element …

Specifies …

string_expression_1

a byte, numeric, or character string or string expression.

string_expression_2

string_expression_n

ANSI Compliance

EXCLAMATION POINT character pairs (!!) are Teradata extensions to the ANSI SQL:2011 standard. Do not use them as concatenation operators.

Solid and broken VERTICAL LINE character pairs (||) are ANSI SQL:2011 compliant forms of the concatenation operator.

Argument Types and Rules

Use the concatenation operator on strings and string expressions of type:

  • Byte
  • If any argument is a byte type, all other arguments must also be byte types.

  • Numeric
  • A numeric argument is converted to a character string using the format for the numeric value. For details about implicit numeric to character data type conversion, see “Implicit Numeric-to-Character Conversion” on page 665

  • Character
  • When the arguments are both character types, but have different server character sets, then implicit string conversion occurs. For details, see “Implicit Character-to-Character Translation” on page 603.

  • UDTs that have implicit casts to a predefined character type.
  • To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.

    Implicit type conversion of UDTs for system operators and functions, including the concatenation operator, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. For details, see Utilities: Volume 1 (A-K).

    For more information on implicit type conversion of UDTs, see Chapter 13: “Data Type Conversions.”

    Result Type and Attributes

    The result of a concatenation operation is a string formed by concatenating the arguments in a left‑to‑right direction.

    Here are the default result type and attributes for arg1 || arg2:

  • If the arguments are byte strings, the result is a byte string.
  • If the arguments are numeric, character strings, or UDTs that are implicitly cast to character strings, the result is a character string.
  • If either argument is null, the result is null.

    The data types and attributes of the arguments determine whether the result type of a concatenation operation is a fixed length or varying length string. Result types appear in the following table, where n is the sum of the lengths of all arguments:

     

    IF this argument …

    Is this data type or attribute …

    THEN the result is this data type or attribute …

    either

    VARBYTE

    VARBYTE(n)

    VARCHAR

    VARCHAR(n)

    numeric

    UDT that is implicitly cast to VARCHAR

    CLOB

    CLOB(n)

    BLOB

    BLOB(n)

    both

    BYTE

    BYTE(n)

    CHARACTER (with same server character set)

    CHARACTER(n)

     

    UDT that is implicitly cast to CHARACTER (with the same server character set)

    CHARACTER (with different server character sets)

    VARCHAR(n)

    UDT that is implicitly cast to CHARACTER (with different server character sets)

    numeric

    When either argument is a character string that specifies the CASESPECIFIC attribute, the result also specifies the CASESPECIFIC attribute.

    Example : Using Concatenation to Create More Readable Results

    Literals, spaces, and the TITLE phrase can be included in the operation definition to format the result heading and improve readability.

    For example, the following definition returns side titles, evenly spaced result strings, and a blank heading.

       SELECT ('Sex ' || sex ||', Marital Status ' || mstat)(TITLE ' ')
       FROM Employee ;
       
       Sex M, Marital Status S 
       Sex F, Marital Status M 
       Sex M, Marital Status M 
       Sex F, Marital Status M 
       Sex F, Marital Status M 
       Sex M, Marital Status M 
       Sex F, Marital Status W
          ...

    Example : Concatenating First Name With Last Name

    Consider a table called names that contains last and first names columns, defined as VARCHAR, as listed here:

       lname         ­fname  
       ------------  ------------
       Ryan          Loretta
       Villegas      Arnando 
       Kanieski      Carol 
       Brown         Alan

    Use string concatenation and a space separator to combine first and last names:

       SELECT fname ||' '|| lname 
       FROM names 
       ORDER BY lname ; 

    The result is:

       ((fname||' ')||lname)
       ---------------------
       Alan Brown 
       Carol Kanieski 
       Loretta Ryan 
       Arnando Villegas 

    Example : Concatenating Last Name With First Name

    Change the SELECT and the separator to obtain last and first names:

       SELECT lname||', '||fname 
       FROM names 
       ORDER BY lname;

    The result is:

       ((lname||', ')||fname) 
       ----------------------
       Brown, Alan 
       Kanieski, Carol 
       Ryan, Loretta 
       Villegas, Arnando 

    Example : Concatenating Byte Strings

    This example shows how to concatenate byte strings. Consider the following table definition:

       CREATE TABLE tsttbla
         (column_1 BYTE(2)
         ,column_2 VARBYTE(10)
         ,column_3 BLOB(128K) );

    The following values are inserted into table tsttbla:

       INSERT tsttbla ('4142'XB, '7A7B7C'XB, '1A1B1C2B2C'XB);

    The following SELECT statement concatenates column_2 and column_1 and column_3:

       SELECT (column_2 || column_1 || column_3) (FORMAT 'X(20)')
       FROM tsttbla ;

    The result is:

       ((column_2||column_1)||column_3)
       --------------------------------
       7A7B7C41421A1B1C2B2C

    The resulting data type is BLOB.

    Concatenating Character Strings Having Different Server Character Sets

    There are special considerations for the concatenation of character strings that specify different server character sets in the CHARACTER SET attribute.

    Implicit translation rules apply. For details, see “Implicit Character-to-Character Translation” on page 603.

    If the strings are fixed strings, then the result is varying with length equal to the sum of the lengths of the strings being concatenated.

    This is true regardless of whether the string lengths are defined in terms of bytes or characters. So, a fixed n-byte KANJISJIS character string concatenated with a fixed m-character UNICODE string produces a VARCHAR(m+n) CHARACTER SET UNICODE result.

    Consider the following table definition:

       CREATE TABLE tab1 
          (cunicode  CHARACTER(4)  CHARACTER SET UNICODE
          ,clatin    CHARACTER(3)  CHARACTER SET LATIN 
          ,csjis     CHARACTER(3)  CHARACTER SET KANJISJIS);

    The following values are inserted into table tab1:

       INSERT tab1 ('abc', 'abc', 'abc');

    The following table illustrates these concatenation properties.

     

    Concatenation

    Result

    Type of Result

    cunicode || clatin

    'abcΔabc'

    VARCHAR(7) CHARACTER SET UNICODE

    clatin || csjis

    'abcabc'

    VARCHAR(6) CHARACTER SET UNICODE

    cunicode || csjis

    'abcΔabc'

    VARCHAR(7) CHARACTER SET UNICODE

    With the exception of KanjiEBCDIC, concatenation of KANJI1 character strings acts as described above. Under KanjiEBCDIC, any adjacent shift-out (<) and shift-in (>) characters within the resulting expression are removed. In this case, the result string is padded as necessary with trailing <single-byte space> characters.

    Examples for Japanese Character Sets

    The following tables show the results of concatenating string expressions under each of the Kanji character sets supported by Teradata Database.

    These examples assume that the string expressions follow the rules defined in the chapter “SQL Data Definition” in SQL Data Types and Literals.

    For an explanation of symbols and other notation in the examples, see “Character Shorthand Notation Used In This Book” on page 1370.

    Example : KanjiEBCDIC

       string_expression_1 || string_expression_2
     

    string_expression_1

    string_expression_2

    Result

    <ABC>

    <DEF>G

    <ABCDEF>G

    <ABC>

    <>

    <ABC>

    <ABC>a

    <DEF>

    <ABC>a<DEF>

    Example : KanjiEUC

       string_expression_1 || string_expression_2
     

    string_expression_1

    string_expression_2

    Result

    ABCm

    DEFg

    ABCmDEFg

    ss3A ss2B m

    ss3C

    ss3A ss2B m ss3C

    Example : KanjiShift-JIS

       string_expression_1 || string_expression_2
     

    string_expression_1

    string_expression_2

    Result

    mnABCX

    B

    mnABCXB

    mnABCX

    g

    mnABCXg