Re: Simply question about TO_NUMBER function

MariMax wrote:
> >
> > SELECT * FROM Table1 WHERE Field1 = TO_CHAR(456)
> >
>
> It don't work because if i have values '456' and '0456' and '00456',
i want
> the query return three records not only the first.

which is why your:
"SELECT * FROM Table1 WHERE TO_NUMBER(Field1) = 456" returns an error like "not valid number", like you said initially.

> I can't define the field NUMERIC because it may be alphanumeric.

OK, then treat it as alphanumeric and get only the strings you want:

create index table1_func_ix1 on table1(ltrim(Field1,'0')); select
*
from table1
where ltrim(Field1,'0') = '456';
and it should now be as fast as possible.

IF you are running a version of Oracle that does not support function-based indexes(!!!), then just add a NUMBER column to the table, populate it with TO_NUMBER(Field1) using PL/SQL (set to null on error exceptions) and index that new column and use it in the query: bingo, function-based indexes on the cheap.

If your Field1 can contain embedded letters in the number, then the above may be your only option, regardless of what Oracle version you're running.

Bottom line: your data is not what it should be for the type of queries you are doing. Columns are attributes, and an attribute in relational design cannot have two types. So you can't store numbers and strings in a single column and expect the RDBMS to figure it out for you automatically.

HTH Received on Fri Feb 04 2005 - 01:14:12 CST