How does numeric comparison work on an Oracle VARCHAR column?
I have a table where two columns are of type VARCHAR2 (3BYTE) and VARCHAR2 (32BYTE). When I make a select ( where col1=10
and where col1='10'
) or ( where col2=70001
or col2='70001'
) query , the number of selected records is the same in each set of where clauses. How did this happen? How does Oracle handle string literals and numeric constants and compare to data regardless of the data type of the column?
But that doesn't work for a VARCHAR2 (128BYTE) column. The request must be in where col3='55555555001'
order to work and where col3=55555555001
throws an ORA-01722 error.
source to share
As stated in the SQL Language Reference :
- During SELECT FROM operations, Oracle converts the data from the column to the target variable type.
- ...
- When comparing a character value to a numeric value, Oracle converts the character data to a numeric value.
Implicit conversion is performed on a table column when the types do not match. This can be seen by tracing in SQL * Plus with some dummy data.
create table t42 (foo varchar2(3 byte));
insert into t42 (foo) values ('10');
insert into t42 (foo) values ('2A');
set autotrace on explain
It works:
select * from t42 where foo = '10';
FOO
---
10
Execution Plan
----------------------------------------------------------
Plan hash value: 3843907281
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T42 | 1 | 3 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FOO"='10')
Note
-----
- dynamic sampling used for this statement (level=2)
But these errors:
select * from t42 where foo = 10;
ERROR:
ORA-01722: invalid number
Execution Plan
----------------------------------------------------------
Plan hash value: 3843907281
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T42 | 1 | 3 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("FOO")=10)
Note the difference in the filter; filter("FOO"='10')
against filter(TO_NUMBER("FOO")=10)
. In the latter case, when comparing with a number, a to_number()
is performed against each row of the table, the result of this conversion is compared to a fixed value. Therefore, if any of the character values cannot be converted, you will get ORA-01722. The applied function will also stop the used index if it is present in this column.
If wondering if you have more than one filter. Oracle may be evaluating them on different orders at different times, so you may not always see ORA-01722 and it pops up sometimes. Let's say you had where foo = 10 and bar = 'X'
. If Oracle thought it might filter out the non-values first X
, it would only apply to to_number()
what was left, and that the smaller sample might not have non-numeric values in foo
. But if you have and bar = 'Y'
, the non values Y
can include non-numeric numbers, or Oracle can filter on first foo
, depending on how selectively it counts the values.
The moral is to never store numeric information as a character type.
I was looking for the AskTom link to bolster the moral, and the first one I looked at conveniently refers to the "reorder predicate" effect, as well as the expression "don't store numbers in varchar2's".
source to share
If you are using a numeric column, or a value and a character column, Oracle converts the values of the character columns to numbers and then converts the numbers to numbers. As if you wrote:
where to_number(col3) = 55555555001
This is why you get an error ORA-01722: invalid number
if one line contains a string (n col3) that cannot be converted to a numeric value.
For this reason, we have a function IS_NUMBER
in our Oracle database that does not throw an error, but returns NULL for values that cannot be converted to numbers. Then you can safely write:
where is_number(col3) = 55555555001
The function is defined as:
CREATE OR REPLACE FUNCTION is_number (p_str IN VARCHAR2)
RETURN NUMBER
IS
l_num NUMBER;
BEGIN
l_num := to_number(p_str);
RETURN l_num;
EXCEPTION
WHEN others THEN
RETURN NULL;
END is_number;
source to share