11

I have the following query in SQLite:

SELECT * FROM t1 ORDER BY t1.field

Where t1.field is a text column containing numbers. Is it posible to force SQLite to consider the values of t1.field as numbers instead of strings (whithout doing ALTER TABLE)? Right now the sort is a pure string one, so 10 goes before 2.

Thank you.

aculich
  • 14,545
  • 9
  • 64
  • 71
diegogs
  • 2,036
  • 2
  • 16
  • 20

1 Answers1

25

Well, found a solution:

SELECT * FROM t1 ORDER BY t1.field + 0

The + 0 part seems to force conversion to number

diegogs
  • 2,036
  • 2
  • 16
  • 20
  • thank you this helped me, out of interest on what platform were you using SQLite on? – S-K' Oct 21 '13 at 19:30
  • 12
    SELECT * FROM t1 ORDER BY CAST(t1.field AS INTEGER) – Martin Ždila Oct 29 '13 at 10:16
  • You can even sort mixed data. If using `ORDER BY t1.field + 0, t1.field`, this will first sort all numbers, then all strings that could not be converted to a number -> `["no", "yes", "1", "5", "10", 50"]` – relet Nov 03 '14 at 12:33
  • It will even order mixed strings: `["10 dogs", "2 dogs", "35 dogs"]` becomes `["2 dogs", "10 dogs", "35 dogs"]` – fpg1503 Jan 20 '15 at 17:32