What happens? <= on two varchars?

Question

In my Oracle db, I noticed some strange behavior when doing a <= comparison on two numbers. And I found the problem. The .net code was passing in one of the values as a DbParameter of type string. And the Oracle column type of the other number was a varchar. So essentially what was happening was a <= on two numbers that were actually varchars.

Can someone explain why the following statement is true in PLSQL?

'10000001' <= '50000'

This is a string comparison which acts differently than a number comparison. — NendoTaka, Nov 17 '17 at 21:13
See the [data type comparison](https://docs.oracle.com/database/121/SQLRF/sql_elements002.htm#SQLRF30027) rules, which explain what is happening. Basically, `’1’` is “less than“ `’5’` when compared as characters in your character set. — Alex Poole, Nov 17 '17 at 21:23
If only there was more than thirty years' worth of database practice emphasising the importance of using the appropriate datatype for the data. — APC, Nov 18 '17 at 22:44
It's not just true in PL/SQL, it's true in SQL, and, I would think, in all languages that distinguish between strings and numbers. Why wouldn't `'1'` be less than `'5'`? — William Robertson, Nov 19 '17 at 00:15

score 1 · Answer 1 · answered Nov 18 '17 at 08:09

Becuase-

SQL> select ascii('10000001') from dual;

ASCII('10000001')
-----------------
               49

SQL> select ascii('50000') from dual;

ASCII('50000')
--------------
            53

SQL> select 'true' from dual where ascii('10000001') < ascii('50000');

'TRU
----
true

What happens? <= on two varchars?

1 Answers1