SQLite ORDER BY string containing number starting with 0

user_1447087 0
Viewed 35284

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?

numeric sql-order-by sqlite sql string
5 Answers

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)

user_83136090

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

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

user_104783590
Related