{"id":198,"date":"2018-07-11T11:44:06","date_gmt":"2018-07-11T15:44:06","guid":{"rendered":"http:\/\/blog.canscans.com\/?p=198"},"modified":"2018-07-11T11:44:06","modified_gmt":"2018-07-11T15:44:06","slug":"how-to-sort-non-numeric-field-numerically-in-mysql","status":"publish","type":"post","link":"https:\/\/waterloobae.com\/blog\/2018\/07\/11\/how-to-sort-non-numeric-field-numerically-in-mysql\/","title":{"rendered":"[DB] How to sort non-numeric field numerically in MySQL"},"content":{"rendered":"<p>It is common to store numeric values on Varchar or non-numeric field for developer&#8217;s convenience. However, it can cause a problem when sorting. For example, 10 comes prior to 9 when sorted.<br \/>\nHere are three tricks thatcan be found on various websites as below.<\/p>\n<h2>1. Casting<\/h2>\n<p>It is possible to sort while casting non-numeric value to numeric.\u00a0 Values must be all numeric, otherwise value will be casted as &#8220;0&#8221;.<br \/>\n[code language=&#8221;php&#8221;]<br \/>\nSELECT * from employee ORDER BY CASE(emp_number as DECIMAL);<br \/>\n[\/code]<br \/>\n&nbsp;<\/p>\n<h2>2. Left padding zeros<\/h2>\n<p>Padding zeros to the left will make all values to be sorted as same as numerically.<br \/>\n[code language=&#8221;php&#8221;]<br \/>\nSELECT * from employee ORDER BY lpad(emp_number, 10, 0);<br \/>\n[\/code]<br \/>\n&nbsp;<\/p>\n<h2>3. Coerced Casting<\/h2>\n<p>By using numeric operation, it is possible to cast non-numeric value to numeric, Then, it can be sorted by numerically.<br \/>\n[code language=&#8221;php&#8221;]<br \/>\nSELECT * from employee ORDER BY emp_number + 1;<br \/>\n[\/code]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>It is common to store numeric values on Varchar or non-numeric field for developer&#8217;s convenience. However, it can cause a problem when sorting. For example, 10 comes prior to 9 when sorted. Here are three tricks thatcan be found on various websites as below. 1. Casting It is possible to sort while casting non-numeric value [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-198","post","type-post","status-publish","format-standard","hentry","category-it-tips"],"_links":{"self":[{"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/posts\/198","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/comments?post=198"}],"version-history":[{"count":0,"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/posts\/198\/revisions"}],"wp:attachment":[{"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/media?parent=198"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/categories?post=198"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/waterloobae.com\/blog\/wp-json\/wp\/v2\/tags?post=198"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}