SUBSTR for Integer datatype

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.
Teradata Employee

SUBSTR for Integer datatype



I am currently working with SUBSTR and when I use it with Integer values , I am getting different results like as follows:



SEL SUBSTR(12345,3,5)  -- Ans: 2345
SyntaxEditor Code Snippet
SEL SUBSTR(34567,3,5) --Ans:     3 (with leading spaces)
SyntaxEditor Code Snippet
SEL SUBSTR('12345',3,5) -- Ans: --345 (If enclosed in Single Qoutes , the answer is correct.)

Please can someone help me understand this discrepancy in the Integer results using SUBSTR. Database version is TD 16.



Senior Supporter

Re: SUBSTR for Integer datatype

you are dealing with implict data type conversions

substr is a function for characters. so the integer is first converted to an char with leading ' '.

It is a better style to do the conversion explicit.

try substr(trim(cast(12345 as varchar(10)),3,5)

you could avoid the trim by using a format statement...


Teradata Employee

Re: SUBSTR for Integer datatype

Actually, explicit CAST will trim the leading spaces for you. (Old Teradata-style "cast" syntax does not.)

Note also that 12345 fits in SMALLINT but 34567 requires INTEGER.

SELECT 12345 as x, TYPE(x), FORMAT(x), CAST(x as VARCHAR(16)) as y, LENGTH(y), x (VARCHAR(16)) as z, LENGTH(z);

SELECT 34567 as x, TYPE(x), FORMAT(x), CAST(x as VARCHAR(16)) as y, LENGTH(y), x (VARCHAR(16)) as z, LENGTH(z);

Note that explicitly supplying a FORMAT logically involves two steps CAST(CAST(x AS FORMAT '99999') AS VARCHAR(5)), though the outer CAST to VARCHAR might be implicit.