/2012/03/28/howto-sort-sqlite-integer/
{"item":{"status":"visible","url":"\/2012\/03\/28\/howto-sort-sqlite-integer\/","trash":"","name":"howto-sort-sqlite-integer","parent":"","title":"HowTo: Sort a Sqlite string column as an integer","date":"2012-03-28 15:16:29","filepath":"posts\/20001471_howto-sort-sqlite-integer.xml.json","type":"post","content":"<img class=\"alignleft size-full wp-image-1213\" style=\"margin-right: 10px; margin-bottom: 10px;\" title=\"information icon\" src=\"http:\/\/www.8bitrobot.com\/media\/uploads\/2012\/01\/information.png\" alt=\"information icon\" width=\"46\" height=\"46\" \/>\nThere are times where you have to work with a data set that isn't typed correctly. \u00a0Say you have a sqlite database where one column is a number but in a string type:\n<pre style=\"padding-left: 30px;\">sqlite> CREATE TABLE stuff (description text, inventory text);\nsqlite> SELECT * FROM stuff;\nforks|4\nspoons|401\nknives|12<\/pre>\nNow, say you need to sort the data based on the integer in a string type (inventory above). \u00a0Here's how you would do it:\n<pre style=\"padding-left: 30px;\">sqlite> select cast(inventory as number) as inv, description from stuff order by inv desc;<\/pre>\nThe output will be:\n<pre style=\"padding-left: 30px;\">401|spoons\n12|knives\n4|forks<\/pre>\n<em>see the <a href=\"http:\/\/sqlite.org\/lang_expr.html\">spec<\/a>.<\/em>"}}