In Oracle when you compare a character and a number in a SQL WHERE clause Oracle converts the character value to a number rather than the other way around. I built a simple example to help me understand how this works. I wanted to see if the results were different depending on whether you converted the number to a character or the other way around, and found out that the results are different.
Here is how I setup the two tables I would join:
create table ntest (ncolumn number);
create table ctest (ccolumn varchar2(2000));
insert into ntest values (1);
insert into ctest values ('1');
insert into ctest values ('x');
commit;
Here is the query with the comparison of the numeric column ntest.ncolumn to the character column ctest.ccolumn and the results:
SQL> select ncolumn,ccolumn 2 from ntest,ctest 3 where 4 ntest.ncolumn=ctest.ccolumn; ERROR: ORA-01722: invalid number
This is the equivalent of adding the to_number conversion function explicitly:
SQL> select ncolumn,ccolumn 2 from ntest,ctest 3 where 4 ntest.ncolumn=to_number(ctest.ccolumn); ERROR: ORA-01722: invalid number
In both cases you get the error because it tries to convert the value ‘x’ to a number.
But, if you explicitly convert the number column to a character you don’t get the error:
SQL> select ncolumn,ccolumn 2 from ntest,ctest 3 where 4 to_char(ntest.ncolumn)=ctest.ccolumn; NCOLUMN CCOLUMN ---------- ------------ 1 1
The two choices of which column to convert produce two different results. When you design your application if you have to compare character and number values you should put in the to_char or to_number to force the conversion to be the way you need it to be, or at least you should be aware that Oracle converts a character to a number by default.
– Bobby
It’s well described in “Data Type Comparison Rules”: http://docs.oracle.com/cd/E11882_01/server.112/e10592/sql_elements002.htm
Sayan,
Thanks for the manual reference. That was very helpful. It has a lot of good information.
– Bobby
Bobby,
Here is an example using your table definitions that likely shows why Oracle Database attempts to convert character data to numbers when implicit data type conversion is required:
INSERT INTO NTEST VALUES (‘0.1234’);
INSERT INTO CTEST VALUES (‘0.1234’);
INSERT INTO NTEST VALUES (‘10.50’);
INSERT INTO CTEST VALUES (‘10.50′);
INSERT INTO NTEST VALUES (’10E10′);
INSERT INTO CTEST VALUES (’10E10’);
INSERT INTO NTEST VALUES (‘10000.1234567890123456789’);
INSERT INTO CTEST VALUES (‘10000.1234567890123456789’);
select ncolumn,ccolumn
from ntest,ctest
where
ntest.ncolumn=ctest.ccolumn;
NCOLUMN CCOLUMN
———- ————————-
.1234 0.1234
10.5 10.50
1.0000E+11 10E10
10000.1235 10000.1234567890123456789
select ncolumn,ccolumn
from ntest,ctest
where
to_char(ntest.ncolumn)=ctest.ccolumn;
NCOLUMN CCOLUMN
———- ————————-
10000.1235 10000.1234567890123456789
You could also see problems if column NCOLUMN was defined as a BINARY_DOUBLE rather than a NUMBER with an unspecified number of digits to the right of the decimal point,
Whenever possible, the correct datatype should be used for table columns to avoid unexpected datatype problems (for example, NLS related issues). This article describes some of the problems that could be experienced when using a column defined as a NUMBER to store date values:
http://hoopercharles.wordpress.com/2010/01/06/date-datatype-or-number-data-type-which-should-be-used/
Charles,
Thanks for your comment and the link to your blog post. Your comment makes a lot of sense in terms of why Oracle chose to convert the character value to number by default.
– Bobby