Re: Strange effects of Cast

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 16 Feb 2009 21:45:46 +0100
Message-ID: <4999D07A.30100_at_gmail.com>

Ken schrieb:
> 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 seems to be related to Bug 7154415.
On 11.1.0.7 results are like what you are expecting to be:

SQL> select * from v$version;

BANNER

---

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production

SQL> Select

   2      Cast(NUM1 As Varchar2(7))            As COL1,
   3      Cast(NUM1 As Varchar2(3))            As COL2,
   4      Cast(NUM1 As Varchar2(9))            As COL3,
   5      NUM1                                 As COL4
   6  From
   7      (
   8      Select
   9          '12345678901234567890'  As NUM1
  10      From
  11          Dual
  12      )

  13 /

COL1 COL COL3 COL4

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

1234567 123 123456789 12345678901234567890

Despite that, i agree with Charles, the proper tool for truncation of strings is the SUBSTR functions family, if you misuse the CAST for that purpose, you are buing side effects, such as implicit conversions etc.

Best regards

Maxim Received on Mon Feb 16 2009 - 14:45:46 CST