Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
366 views
Welcome To Ask or Share your Answers For Others

1 Answer

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
...