41

as the title states:

I have a select query, which I'm trying to "order by" a field which contains numbers, the thing is this numbers are really strings starting with 0s, so the "order by" is doing this...

...
10
11
12
01
02
03
...

Any thoughts?

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

Bigger
  • 1,807
  • 3
  • 18
  • 28
  • How are you getting that result? Are you sure the strings just have leading zeros or are the leading spaces involved as well? – mu is too short Jul 15 '12 at 03:16
  • The string doesn't have leading spaces. I was trying to find a way to sort strings containing numbers (1,2,3...,10,11,12) without them being sorted in the 1,10,2,3,4,5 fashion. I appended manually the leading 0, I tried with spaces too, but I see now casting the column to INTEGER was the smartest thing to do in the first place. – Bigger Jul 15 '12 at 03:39
  • 2
    But `'01'` should come before `'10'` when they're sorted as strings, hence my curiosity. Or are you saying that you really have `'1'` rather than `'01'` in the database? – mu is too short Jul 15 '12 at 03:46
  • I was curious about it too, '01' should come before '10' sorted as strings, the previous sort mechanism was a plain ORDER BY value. I know it's not the jtable which re-sorted the rows, I'm puzzled. – Bigger Jul 15 '12 at 08:34

5 Answers5

78

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);
01
02
03
10
11
12
sqlite>

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.

Community
  • 1
  • 1
Samy Vilar
  • 10,800
  • 2
  • 39
  • 34
34

You could use CAST:

ORDER BY CAST(columnname AS INTEGER)
skinnynerd
  • 663
  • 4
  • 7
3

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);
Ko Nyan
  • 61
  • 1
  • 3
1

CONVERT CAST function using order by column value number format in SQL SERVER

SELECT * FROM Table_Name ORDER BY CAST(COLUMNNAME AS INT);

wilfred
  • 11
  • 4
0

Thanks to Skinnynerd. with Kotlin, CAST worked as follows: CAST fix the problems of prioritizing 9 over 10 OR 22 over 206.

define global variable to alter later on demand, and then plug it in the query:

var SortOrder:String?=null

to alter the order use:

For descendant:

 SortOrder = "CAST(MyNumber AS INTEGER)" + " DESC"

(from highest to lowest)

For ascending:

 SortOrder =  "CAST(MyNumber AS INTEGER)" + " ASC"

(from lowest to highest)

BlackHatVisions
  • 21
  • 1