Using CASE_N with Character Comparison

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

You can specify conditional expressions in the CASE_N function that compare CHAR, VARCHAR, GRAPHIC or VARGRAPHIC data types. The following usage rules apply:

  • A CASE_N partitioning expression can use character or graphic comparison except when the comparison involves KANJI1 or KANJISJIS columns or literal expressions.
  • A CASE_N partitioning expression can use the UPPERCASE qualifier and the following functions: LOWER, UPPER, TRANSLATE, TRIM, VARGRAPHIC, INDEX, MINDEX, POSITION, TRANSLATE_CHK, CHAR2HEXINT.
  • Any string literal referenced within a CASE_N expression must be less than 31,000 bytes.
  • The order of character data used in evaluating the conditional expressions in a CASE_N function is determined by the session collation and case specificity of the expression.
    • If the expression is not part of a PPI, the current session collation is used.
    • If the expression is part of a PPI, evaluation is done using the session collation that was in effect when the table or join index was created, or when the partitioning was modified using the ALTER TABLE statement.
    • The case specificity of column references and literals is determined based on the session default, or an explicit CAST, or a specification in the CREATE TABLE statement when the table was created. The column can be explicitly assigned to be CASESPECIFIC or NOT CASESPECIFIC, and literal expressions can be CAST with these qualifiers.

      If not explicitly specified, the default of NOT CASESPECIFIC is used if Teradata session transaction semantics are in effect. If ANSI session transaction semantics are in effect, the default is CASESPECIFIC.

      For example, if a conditional expression is a combination of NOT CASESPECIFIC expressions and a literal with no case specific qualifier (CASESPECIFIC, NOT CASESPECIFIC), the case specificity will be case specific in ANSI mode sessions and not case specific in Teradata mode sessions.

      All character string comparisons involving graphic data are case specific.

  • In character comparison operations (=, <, >, <=, >=, <>, BETWEEN, LIKE), if a string literal is shorter than the column data to which it is compared, the string literal is treated as if it is padded with a pad character specific to the character set (for example, a <space> character).

    Note that the pad character might not collate to the lowest code point in the collation. For a literal of length n, if the column value being compared precisely matches the literal for the first n characters, but contains a character that collates less than the pad character at position n+1, then the column value will collate less than the string literal.