Re: SQL Help

Dirty fix.
Would

SELECT EDI_PO_NUMBER
FROM EDI
WHERE to_number(EDI_PO_NUMBER) BETWEEN 102 AND 119

      and ascii(EDI_PO_NUMBER) BETWEEN 48 AND 57;

be of any use? It would filter out any field value that started with a text character. It does assume that if the field starts with a number then it will be a pure number. If that is not the case you could try adding a few more restrictions. For instance a length(EDI_PO_NUMBER) might sort out some chaff. Keeping to_number(EDI_PO_NUMBER) immediately after the WHERE statement should ensure it sees the data last.

In message <3395B03A.122C_at_pce.net>

        "Lawrence J. Rizzo" <lrizzo_at_pce.net> writes:

> Hello,
 

> I am having trouble in Oracle 7.3 SQL. I have a VarChar2 column that
> sometimes contains numeric information and other times string. I need
> to query the column for numeric values in a given range. I cannot seem
> to find a function / method to allow me to do something like:
 

> SELECT EDI_PO_NUMBER
> FROM EDI
> WHERE VAL(EDI_PO_NUMBER) BETWEEN 102 AND 119;
 
> I know VAL is not a valid Oracle function but if the BASIC equivalent of
> VAL was available it should work.
 

> I did try using TO_NUMBER but no luck as I get a run time query data
> mismatch error.
 

> Any ideas?
 

> Larry Rizzo
> lrizzo_at_pce.net
Received on Thu Jun 05 1997 - 00:00:00 CDT