Selecting against values outside the ASCII range!!!
Date: 06 Apr 2004 12:04:05 GMT
Message-ID: <Xns94C3E07D73339aglwhtcomau_at_211.29.133.50>
Hi all,
I currently have to do some CAD software development, which is what I
normally do, for a system that is tied back into an Oracle DB, the Oracle
part being new to me.
My situation is that there is a column, call it "col3" in a table
that is defined as char(1) but somehow this column has the value 1, 3, 9,
11, 23, 35 and 43 in it. These are NOT the characters '1', '3', '9' etc but
the actual values is in the value for 'A' is 65 in the ASCII table. This is
causing me angst in that when I attempt to select against this column:
select * from table1 when col3 = 1
for the value 1, I get an error
ORA-01722: invalid number
If I in turn do
select * from table1 when col3 = '+'
where '+' is the ASCII character for value 43, one of the values I have, I
get data. Is there a way that I can somehow compare against the values that
are in the control area, less than 32, of the ASCII table, ie 1, 3, 9, 11,
23? I've looked at TO_CHR() and TO_NUMBER() but to my inexperienced eye,
they don't appear to do what I need.
Any thoughts greatly appreciated,
Andrew Lowe
p.s. I get this when I go through TOAD V7.6 back to Oracle 9i V9.2