Comparing Characters and Numbers in Oracle

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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

4 Responses to Comparing Characters and Numbers in Oracle

    • Bobby says:

      Sayan,

      Thanks for the manual reference. That was very helpful. It has a lot of good information.

      – Bobby

  1. 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/

  2. Bobby says:

    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

Leave a Reply