substring function on numeric and charaster values 1

[ripperfuel]

Case 1:
select substring ('12345678', 3, 3);
gives 345 as result, which is fine.

Case 2:
select substring (12345678, 3, 3);
gives 12 as result, and
select substring(12345678 from 7 for 2);
gives 45 as result.

I realize I used number in case 2. Can you explain why I am getting '12' and '45' for those queries. Thanks.

 

[dnoeth]

select type(12345678), format(12345678), 12345678 (char(11));

INTEGER -(10)9 12345678

Substring only works with CHARs, so there's an automatic typecast. That typecast is a Teradata typecast and returns (for an integer) a right aligned string consisting of 11 characters including three leading blanks.

Btw, an ANSI typecast returns left aligned strings:

select '>' || (12345678 (char(11))) || '<',
'>' || cast(12345678 as char(11)) || '<';

> 12345678< >12345678 <


Other databases (including Standard SQL) would complain about non-matching datatypes...

Dieter