Edit Question - KnowledgeHub

Title Body

An SQL statement like: select * from ( select '000000000000' as x from dual union select '978123456789' as x from dual union select 'B002AACD0A' as x from dual ) /*where x>'000000000000'*/ order by x; Yields: B002AACD0A 000000000000 978123456789 After uncommenting the WHERE-restriction, the result is: B002AACD0A 978123456789 I would have expected the result to be just 978123456789 since B002AACD0A is returned before 000000000000 when running the query without restriction. How can this behavior be explained? And how am I supposed to sort and compare varchars so that they can work together like I can do with integers? Funny enough, when changing the restriction to x>'B002AACD0A' , the result is empty. Changing it to x>978123456789 returns B002AACD0A . I.e. when comparing: B002AACD0A > 978123456789 > 000000000000 But when sorting: 978123456789 > 000000000000 > B002AACD0A When using binary sort explicitely ( order by NLSSORT(x,'NLS_SORT=BINARY_AI') ), the result is B002AACD0A>978123456789>000000000000 and matches the behavior of comparison. But I still do not know why this is happening.

Tags (comma-separated) Cancel