Data Type Evaluation

SQL Functions, Operators, Expressions, and Predicates

Product

Teradata Database

Release Number

15.00

Language

English (United States)

Last Update

2018-09-24

dita:id

B035-1145

lifecycle

Product Category

Teradata® Database

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.