0

I have a database that has a column with a sequence of numbers, and I'm trying to select a range, but the results I am getting are odd.

The column data type is set to VARCHAR, rather than INT since in the future these might contain alphanumeric values, but for now, they are purely numeric.

I tried a statement like "SELECT * FROM table WHERE number >= '1' AND number <= '458'", but I am not getting numbers from 1 to 458, I'm getting a sequence like 1, 2, 3, 4, 10, 11, 12... etc.

There are numbers missing, so I assume SQL is applying a logic that I am not familiar with. This query does give me the expected results if I do the search with integers, without the single quotes.

Can someone help me understand the logic SQL uses with strings, I was under the impression that SQL converted the values to integers if applicable?

Hkachhia
  • 4,463
  • 6
  • 41
  • 76
Razziel06
  • 1
  • 1
    In lexicographical order, `120` comes before `13`. – Álvaro González Oct 25 '21 at 11:41
  • 1
    'If both arguments in a comparison operation are strings, they are compared as strings' https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html – P.Salmon Oct 25 '21 at 11:44
  • 1
    Does this answer your question? [SQL string comparison, greater than and less than operators](https://stackoverflow.com/questions/26080187/sql-string-comparison-greater-than-and-less-than-operators) – Nico Haase Oct 25 '21 at 11:48

1 Answers1

0

When you apply WHERE number >= '1' AND number <= '458' to the column which is VARCHAR then the values are compared as strings, symbol-by-symbol according to current COLLATION, and the string values like '1', '2', '11', '458' matches this condition whereas the values '5', '78', '4581', '01' does not match. And you "loose" some values.

If you want to obtain continuous numbers list then you must use numeric compare. And hence you must convert string-type value of number column to numeric datatype. You may do this explicitly (for example, with CAST() function) or implicitly (for example, with number + 0 expression).

Akina
  • 39,301
  • 5
  • 14
  • 25