15.00 - Data Type Evaluation - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

Data Type Evaluation

Different data types define equality and inequality differently. The following table explains the foundations for how the various data types are compared:

 

This data type …

Is evaluated in this way …

Numeric

Algebraically, with negatives considered to be smaller irrespective of their absolute value.

Byte

Bit-by-bit from left to right. A 0 bit is less than a 1 bit.

  • If every pair comparison is equal, then the two byte string are equal.
  • If any pairwise comparison is not equal, then that comparison determines the result.
  • If two byte strings of different lengths are compared, then the shorter string is padded to the right with binary zeros to make the lengths equal prior to making the comparison.
  • Character-by-character from left to right. Exact comparisons depend on the collation sequence assigned and whether the comparison is case specific or case blind.

    The available collations are:

  • ASCII
  • EBCDIC
  • MULTINATIONAL
  • CHARSET_COLL
  • JIS_COLL
  • If every pairwise comparison is equal, then the two character strings are equal.

    If any pairwise comparison is not equal, then that comparison determines the result.

    For more information on character comparison, see “Character String Comparisons” on page 505.

    Character

    Chronologically.

    For information on how Time Zone affects Time comparison, see “Time Zone Sort Order” on page 833.

    DateTime

    Interval

    According to sign and magnitude.

    Period

    Assuming p1 and p2 are Period expressions or derived periods, the evaluation of a Period comparison predicate uses the following logic:

    IF BEGIN(p1) = BEGIN(p2) is TRUE, return END(p1) operator END(p2)
    ELSE return (BEGIN(p1) operator BEGIN(p2))

    For details on BEGIN and END, see Chapter 23: “Period Functions and Operators.”

    UDT

    According to the ordering definition of the UDT.

    Teradata Database generates ordering functionality for distinct UDTs where the source types are not LOBs. To create an ordering definition for structured UDTs or distinct UDTs where the source types are LOBs, or to replace system-generated ordering functionality, use CREATE ORDERING.

    For more information on CREATE ORDERING, see SQL Data Definition Language.