Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.stanford.edu!sn-xit-02!sn-xit-06!sn-post-02!sn-post-01!supernews.com!corp.supernews.com!not-for-mail From: DA Morgan Newsgroups: comp.databases.oracle.server Subject: Re: Simply question about TO_NUMBER function Date: Wed, 02 Feb 2005 16:39:50 -0800 Organization: Ye 'Ol Disorganized NNTPCache groupie Message-ID: <1107391028.333265@yasure> User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 References: <36d5dfF50r1hvU1@individual.net> In-Reply-To: <36d5dfF50r1hvU1@individual.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Cache-Post-Path: yasure!unknown@oracle.advtechserv.com X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/) X-Complaints-To: abuse@supernews.com Lines: 44 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:234861 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@x.washington.edu (replace 'x' with 'u' to respond)