Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Wierd problem, max(column_name) gives different result than max(to_number(column_name))

Re: Wierd problem, max(column_name) gives different result than max(to_number(column_name))

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 21 Sep 1998 16:52:47 GMT
Message-ID: <360a8400.13603250@192.86.155.100>


A copy of this was sent to ramdan_at_mailexcite.com (tony) (if that email address didn't require changing) On 21 Sep 1998 10:31:02 -0500, you wrote:

>
>I have a table that includes an equipment number (eqnum). It is a varchar
>field. When I do a
>
> select max(eqnum) I get 1044241
>
> when i do a
>
> select max(to_number(eqnum) I get 1080101
>
>the lengths are the same a
>SELECT max(eqnum), max(to_number(eqnum)), length(max(eqnum)),
>length(max(to_number(eqnum)))
>gives
>1044241 1080101 7 7
>
>why the difference??
>isn't a number in a varchar field the same as a number in an integer field?

nope, no way, not a chance....

>
>respond to group or ramdan_at_mailexcite.com

I bet 1080101 has a leading blank.

SQL> create table t1 ( eqnum varchar2(10) ); Table created.

SQL> insert into t1 values ( '1044241' ); 1 row created.

SQL> insert into t1 values ( ' 1080101' ); 1 row created.

SQL> select max( eqnum ) from t1;

MAX(EQNUM)



1044241

SQL> select max( to_number(eqnum) ) from t1;

MAX(TO_NUMBER(EQNUM))


              1080101

SQL> select max(eqnum) max1, max(to_number(eqnum)) max2,   2 length(max(eqnum)) l1, length(max(to_number(eqnum))) l2   3 from t1
  4 /

MAX1 MAX2 L1 L2 ---------- ---------- ---------- ---------- 1044241 1080101 7 7

SQL> select eqnum, length(eqnum) from t1;

EQNUM LENGTH(EQNUM)
---------- -------------

1044241                7
 1080101               8


The length of the to_number(eqnum) might be 7, but the length of eqnum is different....  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Sep 21 1998 - 11:52:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US