Explicit Casting

Database
Enthusiast

Explicit Casting

Hi,

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?

Thanks,

Govi

Tags (2)
3 REPLIES
Junior Supporter

Re: Explicit Casting

Govi:

CAST is the preferred method.

In your second query there is an implicit conversion:

 BTEQ -- Enter your SQL request or BTEQ command:

select type(1111);

 *** Query completed. One row found. One column returned.

 *** Total elapsed time was 1 second.

Type(1111)

---------------------------------------

SMALLINT

SMALLINT=> 6 Characters (5 digit +1 sign): <space>,<sign>,1,1,1,1 => CHAR (4) the four first chars: <space>,<space>,1,1 = '  11'.

HTH.

Cheers.

Carlos.

Senior Apprentice

Re: Explicit Casting

Hi Govi,

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.

Dieter

Enthusiast

Re: Explicit Casting

@Carlos, Dieter: Thank you very much!! Your explanations helped me a lot. :)