Compare strings as numbers in SQLite3

Question

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.

diegogs · Accepted Answer · 2010-11-17 13:08:32Z

27

Well, found a solution:

SELECT * FROM t1 ORDER BY t1.field + 0

The + 0 part seems to force conversion to number

answered Nov 17, 2010 at 13:08

diegogs

2,0762 gold badges16 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

S-K' Over a year ago

thank you this helped me, out of interest on what platform were you using SQLite on?

Martin Ždila Over a year ago

SELECT * FROM t1 ORDER BY CAST(t1.field AS INTEGER)

relet Over a year ago

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"]

fpg1503 Over a year ago

It will even order mixed strings: ["10 dogs", "2 dogs", "35 dogs"] becomes ["2 dogs", "10 dogs", "35 dogs"]

Collectives™ on Stack Overflow

Compare strings as numbers in SQLite3

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related