Sqlite Order by String Containing Number Starting with 0

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);
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.

sqlite order by string containing number with comma delimiter

If the commas are used consistently, you can use:

order by length(cases), cases

Otherwise, you can remove the commas and convert:

order by cast(replace(cases, ',', '') as int)

SQLITE order by numeric and not alphabetic

You get alphabetic order if the values are strings.

To change the table so that all Classement values are numbers, ensure that the column type is not a text type, and use this:

UPDATE tableau SET Classement = CAST(Classement AS NUMBER);

SQLite sort varchar that contains letter and number

You can use substr to first order by the first alphabet character and then by the number following it.

select * from tablename
order by substr(traffic_num,1,1), cast(substr(traffic_num,2) as signed integer)

SQLite - order by numbers inside string

So it would be {Season} {Year}

If the numbers are consistently located after the first space in the string, we can use string functions to extract them as follows:

select col
from mytable
order by substr(col, instr(col, ' ') + 1) + 0, col

SQL order string as number

If possible you should change the data type of the column to a number if you only store numbers anyway.

If you can't do that then cast your column value to an integer explicitly with

select col from yourtable
order by cast(col as unsigned)

or implicitly for instance with a mathematical operation which forces a conversion to number

select col from yourtable
order by col + 0

BTW MySQL converts strings from left to right. Examples:

string value  |  integer value after conversion
--------------+--------------------------------
'1'           |  1
'ABC'         |  0   /* the string does not contain a number, so the result is 0 */
'123miles'    |  123 
'$123'        |  0   /* the left side of the string does not start with a number */

How to order query by number first and then by string in the same field

WITH empleados(legajo) AS (
   VALUES
     ('A0001-4'::text)
    ,('123.345-56')
    ,('ve')
    ,('123')
    ,('123 ve')
   ) 
SELECT *
FROM   empleados
ORDER  BY CASE WHEN legajo ~ '\D' THEN 1000000000::int
                                  ELSE to_number(legajo, '999999999')::int END
      ,legajo;

~ is the regular expression operaor.

\D is the classs shorthand for non-digits.

Rows with non-digit characters in legajo (legajo ~ '\D') come later.

-> SQLfiddle demo

Never use SIMILAR TO, it's an utterly pointless operator.

Prevent SQLite query from stripping leading zeros from numeric strings?

I believe you issue is not with substr stripping characters as this works as expected e.g. :-

Then running query SELECT substr(col1,2,2) || substr(col2,2,2) as mode FROM stripping

results in (as expected):-

Rather, your issue is likely how you subsequently utilise mode in which case you may need to use a CAST expression CAST expressions

For example the following does what is possibly happening :-

`SELECT  substr(col1,2,2) || substr(col2,2,2) as mode, CAST(substr(col1,2,2) || substr(col2,2,2) AS INTEGER) AS oops  FROM stripping`

resulting in :-

---

Related Topics