THEN/ELSE Character Type Expressions

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

The following rules apply to CASE expressions where the data types of all of the THEN/ELSE expressions are character:

  • The result of the CASE expression is also a character data type, with the length equal to the maximum length of the different character data types of the THEN/ELSE expressions.
  • If the data types of all of the THEN/ELSE expressions are CHARACTER (or CHAR), the result data type is CHARACTER. If one or more expressions are VARCHAR (or LONG VARCHAR), the result data type is VARCHAR.
  • The server character set of the result is determined as follows:
    • If the CASE expression contains 1 nonliteral character expression and 1 or more literals, then Teradata Database tries to translate every literal to the character set of the nonliteral. If the translations are successful, then the character set of the nonliteral is used for the result data type. If the translations are not successful, the server character set of the result is Unicode.
    • If the CASE expression contains more than 1 nonliteral character expression and 1 or more literals, then:

      If all of the nonliteral expressions have the same character set, then Teradata Database uses this character set as the common data type. Otherwise, if the nonliteral expressions have differing character sets, then Teradata Database uses the Unicode character set as the common data type.

      Teradata Database tries to translate every literal to the character set of the common data type. If the translations are successful, then the result data type has the character set of the common data type. If the translations are not successful, the server character set of the result is Unicode.