Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Wierd problem, max(column_name) gives different result than max(to_number(column_name))
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)
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
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