Re: Simply question about TO_NUMBER function

"MariMax" <massimo.panero_at_powertiscali.it> wrote in message news:36e7tpF51838nU1_at_individual.net...
>
> "Tom Dyess" <tdyess_at_dysr.com> ha scritto nel messaggio
> news:u1fMd.23624$t67.10929_at_bignews5.bellsouth.net...
>>
>> 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.
> I can't define the field NUMERIC because it may be alphanumeric.
>
>

> SELECT * FROM Table1 WHERE GET_VAL(Field1) = 456
>it works, but i see that my function GET_VAL work 8/9 times slowly than
>TO_NUMBER function, and used with big tables or in cycles is not so good.

    Hmm. The function shouldn't run 8-9 times slower than to_number. How are you testing it? If it's based on the result coming back fast with the "invalid number" error, that's not valid because the query stopps when it hits a non-castable datatype (i.e. AAA) as opposed to running through the entire recordset/cursor. I would say what your doing is the best way to do it. Remember, unless you have EE (a specific module, forgot which one, Advanced Query I think it's called) you lose the use of your index on Field1 because you are calling a function on the field in the where clause as opposed to the field itself. Depending on how much padding you have, it may be faster to call the to_number on the filter (varchar2 456) x times than putting a function on the field:

i.e.

SELECT * FORM TABLE1
WHERE Field1 = '456'

OR Field1 = '0456'
OR Field1 = '00456'
OR Field1 = '000456'

or

SELECT * FROM TABLE1
WHERE Field1 in ('456','0456','00456','000456')

Ad Nausium. I would run speed testing on both to see which is faster. Anyhow, if you must run a function on the field1 column, I would say your method is about the fastest I can think of. You might want to make sure your speed testing method is correct.

-- 
Tom Dyess
OraclePower.com