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:
If any argument is a byte type, all other arguments must also be byte types.
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
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.
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 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 |
|
|
|
|
|
|
|
|
|
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 |