{"id":726,"date":"2014-07-10T11:37:00","date_gmt":"2014-07-10T18:37:00","guid":{"rendered":"http:\/\/blog.ls-al.com\/?p=726"},"modified":"2014-07-10T11:37:00","modified_gmt":"2014-07-10T18:37:00","slug":"mysql-sort-strings-like-numbers","status":"publish","type":"post","link":"https:\/\/blog.ls-al.com\/mysql-sort-strings-like-numbers\/","title":{"rendered":"MySQL Sort Strings Like Numbers"},"content":{"rendered":"<p>There are a lot of articles out on the web that have suggestions around sorting a string like a number.  One quick way with simple strings is \"SELECT st FROM table ORDER BY st * 1\".  For me it was a little tricky because I needed to sort on a field called section and look like this \"1.1.1\".  All fine until you have \"1.1.10\" and \"1.12.1\" etc.<\/p>\n<p>For me the following worked.<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\nmysql&gt; SELECT section FROM tasks ORDER BY SUBSTRING_INDEX(section,'.',1),SUBSTRING_INDEX(SUBSTRING_INDEX(section,'.',2),'.',-1) * 1,SUBSTRING_INDEX(section,'.',-1) * 1;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>There are a lot of articles out on the web that have suggestions around sorting a string like a number.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[49],"tags":[],"class_list":["post-726","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/blog.ls-al.com\/wp-json\/wp\/v2\/posts\/726","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ls-al.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ls-al.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ls-al.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ls-al.com\/wp-json\/wp\/v2\/comments?post=726"}],"version-history":[{"count":0,"href":"https:\/\/blog.ls-al.com\/wp-json\/wp\/v2\/posts\/726\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.ls-al.com\/wp-json\/wp\/v2\/media?parent=726"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ls-al.com\/wp-json\/wp\/v2\/categories?post=726"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ls-al.com\/wp-json\/wp\/v2\/tags?post=726"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}