15.00 - Concatenation Operator - Teradata Database

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