Oracle-L: RE: How to avoid ora-1722 invalid number

From: kkennedy <kkennedy_at_firstpoint.com>
Date: Tue, 06 Aug 2002 09:45:08 -0800
Message-ID: <F001.004AC582.20020806094508@fatcity.com>

Hi Rick,

I've dealt with this before but it has been a while. Have you tried something along the lines of:

select to_number(field1)
from (select field1 from tab1 where field1 not like '%\_%' escape '\')

This still does the full table scan but it won't return the non-numeric values to the to_number function.

If you have other non-numerics you could do something more esoteric like where translate(field1,'A0123456789','A') is null

Kevin Kennedy
First Point Energy Corporation

If you take RAC out of Oracle you get OLE! What can this mean?

-----Original Message-----
Sent: Tuesday, August 06, 2002 9:49 AM
To: Multiple recipients of list ORACLE-L

Hi,

I have a table with a column defined as varchar. This field will always have numbers but some of them have underscores in the data which is also valid
data. I want to be able to query the table using a to_number() in the where clause. Obviously I am getting ora-1722.

I cannot apply clause to try to filter the '_' data out as it will do a FTS and apply to_number to that field anyway.

Any suggestions?

Thanks
Rick

SELCT *
FROM <table>
WHERE TO_NUMBER(<field>) BETWEEN 10 AND 10000;

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Rick_Cale_at_teamhealth.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: kkennedy
INET: kkennedy_at_firstpoint.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------