String comparison Teradata optimizer process

Database
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

SELECT

case

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

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

     else 'equal'

     end as result

How greater than and less then work with strings?

2 REPLIES

Re: String comparison Teradata optimizer process

Hi,
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,
http://community.teradata.com/t5/Database/Getting-a-unique-value-for-VARCHAR-field/td-p/54125
Sql Code,
SELECT
(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,
case
when 'AAZ' > ' AB' then 'greater'
WHEN 'AAZ' < ' AB' then 'small'
else 'equal'
end as resultc;
Tags (1)
N/A

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