Re: Simply question about TO_NUMBER function

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)