Please explain how this wotks.
SEL SUBSTR(CAST(12345 AS VARCHAR(5)),1,3)
This gives "123" as OP
gives only "12", why does when passing integer/number it always reduces one explictly and gives output?
For integer first byte is a Signed Byte, which is used for storing sign "+/-". Thats why for positive value, you are getting " 12" as output.
SUBSTR(CAST(12345 AS VARCHAR(5)),1,3)
This is an explicit cast to a VARCHAR using ANSI style syntax, which results in a left-aligned string, the result is the same as a SUBSTR('12345', 1, 3).
This implicit cast to a VARCHAR using Teradata style syntax, which results in a right-aligned string based on the FORMAT. 12345 is a SMALLINT with a FORMAT '-(5)9', up to five digits and a leading sign, the result is the same as a SUBSTR(' 12345', 1, 3).