0

New to sql and db2 queries. I have a table that will contain both numbers and alphanumeric values. They column is defined as VARCHAR. I am trying to compare the column with another value (which is more likely numbers only) if they fall within a range. 

SELECT COL1
FROM TABLE1
WHERE COL1 >= RANGE_START AND COL1 <= RANGE_END;

Consider the table contains below values in COL1. 

106
1000003000
A105
ABCDD

RANGE_START as 1000000000
RANGE_END as 3000000000

When I run the query it also gives 1000003000 and 106 as a satisfying condition. Why would it give 106? Any help in this is greatly appreciated.

Thanks

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
A Rao
  • 11
  • 5
  • 1
    I tried to use CAST( AS BIGINT) but that fails with invalid character string error. – A Rao Feb 16 '16 at 16:46
  • If you want to understand why the string '106' is greater than the string '1000000000', try `SELECT COL1 FROM TABLE1 ORDER BY COL1`. This comparison is performed in character-sorted order, not numeric order. – Ian Bjorhovde Feb 16 '16 at 17:55

0 Answers0