Concatenating Character Strings Having Different Server Character Sets

Teradata Vantage™ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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.

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.