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>