Re: Simply question about TO_NUMBER function

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1107391028.333265_at_yasure...
> MariMax wrote:
>
>> If I have a table like this:
>> CREATE TABLE Table1
>> (
>> Field1 VARCHAR2(10),
>> Field2 VARCHAR2(10),
>> Field3 VARCHAR2(10)
>> );
>> whit this values:
>> Field1 Field2 Field3
>> ---------- ---------- ----------
>> 123 AAA AAA
>> 456 BBB BBB
>> 789 CCC CCC
>> XYZ DDD EEE
>>
>> I see that if I run the query:
>> SELECT * FROM Table1 WHERE TO_NUMBER(Field1) = 456
>> it return an error like "not valid number".
>>
>> To resolve the problem I 've created a function:
>> CREATE OR REPLACE FUNCTION GET_VAL (X IN VARCHAR2) RETURN NUMBER IS
>> BEGIN
>> RETURN TO_NUMBER(STRVAL);
>> EXCEPTION
>> WHEN OTHERS THEN RETURN NULL;
>> END;
>>
>> Now if I run the query:
>> 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.
>>
>> Someone have another idea to resolve the problem quickly?
>> Thanks!
>
> What is the business case? What is it you are actualy trying to do
> and with what version of Oracle?
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace 'x' with 'u' to respond)

Can you do something like this:

SELECT * FROM Table1 WHERE Field1 = TO_CHAR(456)

-- 
Tom Dyess
OraclePower.com