String comparison Teradata optimizer process

The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

String comparison Teradata optimizer process

How Teradata perform comparison on strings using Logical and Conditional Operators?


For example



     when 'AAZ' > 'AB' then 'greater'

     WHEN 'AAZ' < 'AB' then 'small'

     else 'equal'

     end as result

How greater than and less then work with strings?


Re: String comparison Teradata optimizer process

From Docementation ,
Comparison of Character Strings of Unequal Length
"If character strings of unequal length are being compared, the shorter of the two is padded on the right with pad characters before the comparison occurs."
From Forum,
Sql Code,
(HASHBUCKET( hashrow(' AB' ) (BYTE(4))) / ((HASHBUCKET()+1)/65536) * 65536. +
HASHBUCKET(SUBSTR( hashrow(' AB' ) ,3,2) (BYTE(4))) / ((HASHBUCKET()+1)/65536) -
HASHBUCKET( hashrow(' AB' ) (BYTE(4))) / ((HASHBUCKET()+1)/65536) / 32768 * 4294967296)
as xx2,
(HASHBUCKET( hashrow('AAZ' ) (BYTE(4))) / ((HASHBUCKET()+1)/65536) * 65536. +
HASHBUCKET(SUBSTR( hashrow('AAZ' ) ,3,2) (BYTE(4))) / ((HASHBUCKET()+1)/65536) -
HASHBUCKET( hashrow('AAZ' ) (BYTE(4))) / ((HASHBUCKET()+1)/65536) / 32768 * 494967296)
as x12,
when 'AAZ' > ' AB' then 'greater'
WHEN 'AAZ' < ' AB' then 'small'
else 'equal'
end as resultc;
Tags (1)
Senior Apprentice

Re: String comparison Teradata optimizer process

@eejimkos: padded on the right means 'AB ' not ' AB' which is left-padded.


The OP probably asked for the rules which define greater/less, in Teradata & Standard SQL it's based on the session collation, i.e. sort order. There's a chapter in the SQL Functions, Operators, Expressions, and Predicates manual on that topic.


Additionally the shorter string is padded to match the length of the other.

E.g. Oracle doesn't follow that rule, thus treats 'A'  and 'A ' as different values