Re: Strange effects of Cast

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 17 Feb 2009 21:26:04 +0100
Message-ID: <499b1d63$0$195$e4fe514c_at_news.xs4all.nl>

Ken schreef:
> I am getting unexpected results from a Select statement which uses
> Cast to tuncate strings.
>
> In the query below although I get the expected result in COL1 and COL2
> COL3 and COL4 return only three characters. In fact if I swap COL1 and
> COL2 in the query then all columns are three characters long.
>
> I have not been able to find any references to this problem. Has
> anyone else come accross this behavior?
>
> Select
> Cast(NUM1 As Varchar2(7)) As COL1,
> Cast(NUM1 As Varchar2(3)) As COL2,
> Cast(NUM1 As Varchar2(9)) As COL3,
> NUM1 As COL4
> From
> (
> Select
> '12345678901234567890' As NUM1
> From
> Dual
> )
>
>
> COL1 COL COL3 COL4
> ------- --- --------- --------------------
> 1234567 123 123 123
> 1 row selected.
>
> select * from v$version;
>
> BANNER
> ----------------------------------------------------------------
> Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
> PL/SQL Release 9.2.0.5.0 - Production
> CORE 9.2.0.6.0 Production
> TNS for 32-bit Windows: Version 9.2.0.5.0 - Production
> NLSRTL Version 9.2.0.5.0 - Production

It will/might (depending on versions) work if you use:

Select

     NUM1                                 As COL4,
     Cast(NUM1 As Varchar2(9))            As COL3,
     Cast(NUM1 As Varchar2(7))            As COL1,
     Cast(NUM1 As Varchar2(3))            As COL2
From
     (
     Select
         '12345678901234567890'  As NUM1
     From
         Dual
     )

Note how the operation slowly "eats" your column NUM.

Shakespeare Received on Tue Feb 17 2009 - 14:26:04 CST