I tried the following two queries.
SELECT CAST(1111 AS CHAR(4));
SELECT 1111 (CHAR(4));
Though both these queries use explicit casting, former is ANSI compliant whereas the latter is not. I thought these queries will produce identical results but below is what I encountered.
Query 1 returned '1111' -- one one one one
Query 2 returned ' 11' -- <space> <space> one one
Can anyone please share your understanding on this?
CAST is the preferred method.
In your second query there is an implicit conversion:
BTEQ -- Enter your SQL request or BTEQ command:
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
SMALLINT=> 6 Characters (5 digit +1 sign): <space>,<sign>,1,1,1,1 => CHAR (4) the four first chars: <space>,<space>,1,1 = ' 11'.
you can also do a FORMAT(1111) which directly shows the formatting rule.
Teradata style cast uses this format as is, resulting in leading blanks, whereas ANSI CAST trims the leading blanks.