SQLite ORDER BY string containing number starting with 0

You can use CAST http://www.sqlite.org/lang_expr.html#castexpr to cast the expression to an Integer.

sqlite> CREATE TABLE T (value VARCHAR(2));sqlite> INSERT INTO T (value) VALUES ('10');sqlite> INSERT INTO T (value) VALUES ('11');sqlite> INSERT INTO T (value) VALUES ('12');    sqlite> INSERT INTO T (value) VALUES ('01');sqlite> INSERT INTO T (value) VALUES ('02');sqlite> INSERT INTO T (value) VALUES ('03');sqlite> SELECT * FROM T ORDER BY CAST(value AS INTEGER);010203101112sqlite>

if I do this: "...ORDER BY (field+1)" I can workaround this, because somehow the string is internally being converted to integer. Is the a way to "officially" convert it like C's atoi?

Well thats interesting, though I dont know how many DBMS support such an operation so I don't recommend it just in case you ever need to use a different system that doesn't support it, not to mention you are adding an extra operation, which can affect performance, though you also do this ORDER BY (field + 0) Im going to investigate the performance

taken from the sqlite3 docs:

A CAST expression is used to convert the value of to a different storage class in a similar way to the conversion that takes place when a column affinity is applied to a value. Application of a CAST expression is different to application of a column affinity, as with a CAST expression the storage class conversion is forced even if it is lossy and irrreversible.

4.0 Operators
All mathematical operators (+, -, *, /, %, <<, >>, &, and |) cast both operands to the NUMERIC storage class prior to being carried out. The cast is carried through even if it is lossy and irreversible. A NULL operand on a mathematical operator yields a NULL result. An operand on a mathematical operator that does not look in any way numeric and is not NULL is converted to 0 or 0.0.

I was curios so I ran some benchmarks:

>>> setup = """... import sqlite3... import timeit... ... conn = sqlite3.connect(':memory:')... c = conn.cursor()... c.execute('CREATE TABLE T (value int)')... for index in range(4000000, 0, -1):...     _ = c.execute('INSERT INTO T (value) VALUES (%i)' % index)... conn.commit()... """>>> >>> cast_conv = "result = c.execute('SELECT * FROM T ORDER BY CAST(value AS INTEGER)')">>> cast_affinity = "result = c.execute('SELECT * FROM T ORDER BY (value + 0)')">>> timeit.Timer(cast_conv, setup).timeit(number = 1)18.145697116851807>>> timeit.Timer(cast_affinity, setup).timeit(number = 1)18.259973049163818>>>

As we can see its a bit slower though not by much, interesting.

You could use CAST:

ORDER BY CAST(columnname AS INTEGER)

In ListView with cursor loader!

String projection= some string column;String selection= need to select;String sort="CAST ("+ YOUR_COLUMN_NAME + " AS INTEGER)";CursorLoader(getActivity(), Table.CONTENT_URI, projection, selection, selectionArgs, sort);