Try this:
SELECT * FROM (
SELECT * FROM <table>
WHERE <field> NOT LIKE '%_%'
)
WHERE TO_NUMBER(<field>) BETWEEN 10 AND 10000;
JP
On Tuesday 06 August 2002 18:48, you wrote:
> 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;
--
Pruner Jan
jan_at_pruner.cz
http://jan.pruner.cz/
-----------------------------
Only Robinson Crusoe had all his work done by Friday
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jan Pruner
INET: jan_at_pruner.cz
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).
Received on Tue Aug 06 2002 - 12:08:49 CDT
