in oracle db how does comparision workks when we provide the string in place of a number?

Question

Suppose I have a query like this (not so practical, and I'm aware this is not the approach)

SELECT * 
FROM CO.CUSTOMERS 
WHERE CUSTOMER_ID = '1' 
  AND length((SELECT FULL_NAME 
              FROM CO.CUSTOMERS 
              WHERE CUSTOMER_ID=1)) > '35';

-- in the last as you can see I'm comparing it with a string ( i think )

But still the above query is giving result, so I would like to know is the comparison is being done with the string or the integer that is made out of string.

Answer 1

In such cases, Oracle performs implicit datatype conversion. If it manages to do that, it will return result. Otherwise, it would fail.

For example, you could have compared length > 'A2'; as A2 (a string) can't be converted to numeric value, you'd get an error.

SQL> select count(*) from dual where length(dummy) > '-25';

  COUNT(*)
----------
         1

SQL> select count(*) from dual where length(dummy) > 'A2';
select count(*) from dual where length(dummy) > 'A2'
                                                *
ERROR at line 1:
ORA-01722: invalid number


SQL>

Answer 2

Oracle applies implicit data conversion here as documented in docs.oracle.com..\Data-Type-Comparison-Rules.html.

In my opinion, the explantion in the docs is rather poor:

Oracle Database automatically converts a value from one data type to another when such a conversion makes sense.

In your query you compare numbers with strings. In this case, Oracle converts the string into a number implicitly. Why does this "make sense"? Because where num_col < '012' with num_col = 1 would return false, if converted to where '1' < '012', because '1' comes after '0' in string sorts, whereas where 1 < 12 returns true as expected.

Oracle recommends to always convert explicitly (or better avoid the conversion in the first place by using the appropriate data types for table columns and the appropriate literal type as well).

If you rely on implicit conversion, it may fail unexpectedly. With numbers, this can easily happen for instance, when the session setting for the decimal separator is different from what you expect. where num_col < '1.0' can fail, when used by a person who uses the comma as the decimal separator. This becomes even more important, when you have queries in PL/SQL functions, procedures or packages, because the code would compile with where num_col < '1,0', while it would not with where num_col < 1,0. So in the latter case you get an error when compiling and can fix the error immediately, in the former case you'll have broken code leading to a runtime error someday.

Another important point is that implicit conversion can slow down queries immensely. where string_col = 1 will lead to the DBMS having to convert the string column to a number for each and every row in order to do the comparison, while it might be able to use an index with where string_col = '1' and get the desired rows instantly. (The real solution here would of course be to change the column type to the appropriate numeric data type instead.)