Simply question about TO_NUMBER function

From: MariMax <massimo.panero_at_powertiscali.it>
Date: Thu, 3 Feb 2005 00:18:06 +0100
Message-ID: <36d5dfF50r1hvU1@individual.net>

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! Received on Wed Feb 02 2005 - 17:18:06 CST