How to order strings as numbers in Sqlite3

A client had this table ordered by a string field called “name”. But she had inserted a lot of stuff using this method:

3.1.1
3.1.10
3.1.11
3.1.2
3.1.3
3.1.4
3.1.5
3.1.6
3.1.7
3.1.8
3.1.9

Number 10 is going before number 2, that’s impossible!

The problem comes from the design, that’s not a nice way to order, but sometimes a workaround is just perfect:

ORDER BY ABS(replace(name,'.',''))

First we replace all “.” characters and later we use “ABS” to convert the string to a number. And finally we order by that number :).

Post a comment.