Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to avoid ora-1722 invalid number
well use exception handling....i.e to say when the control comes to
when value_error exception it is understood that the field was not pure
number hence u can handle the situation as u desire else carry on as
intended.
rgds,
Ams.
www.medicomsoft.com
-----Original Message-----
Rick_Cale_at_teamhealth.com
Sent: Tuesday, August 06, 2002 8:49 PM
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-LReceived on Wed Aug 07 2002 - 01:38:22 CDT
(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: Amjad Saiyed INET: amjad_at_medicomsoft.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).
![]() |
![]() |