{"id":1114,"date":"2016-01-18T10:13:13","date_gmt":"2016-01-18T10:13:13","guid":{"rendered":"http:\/\/codethataint.com\/blog\/?p=1114"},"modified":"2016-01-18T10:13:13","modified_gmt":"2016-01-18T10:13:13","slug":"oracle-sorting-alphanumeric-values","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/oracle-sorting-alphanumeric-values\/","title":{"rendered":"Oracle Sorting Alphanumeric Values"},"content":{"rendered":"<p>While Doing sort in oracle the Normal sort works differently for the following Data<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE TABLE TestTable(Col1 VARCHAR2(20));\r\n\r\ninsert into TestTable values('A11');\r\ninsert into TestTable values('A260');\r\ninsert into TestTable values('A10');\r\ninsert into TestTable values('A5');\r\n<\/pre>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nA11\r\nA260\r\nA10\r\nA5\r\n<\/pre>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n  SELECT * \r\n    FROM TestTable \r\n   ORDER BY col1;\r\n<\/pre>\n<p><strong>Output<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nA10\r\nA11\r\nA260\r\nA5\r\n<\/pre>\n<p>You may expected <strong>A5<\/strong> before <strong>A10<\/strong> and <strong>A11<\/strong>. But It would have been before A10 and A11 when it is <strong>A05<\/strong>. The workaround for this is as below.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT * \r\n  FROM TestTable\r\n ORDER BY lpad(col1,9999999);\r\n<\/pre>\n<p><strong>Output<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nA5\r\nA10\r\nA11\r\nA260\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>While Doing sort in oracle the Normal sort works differently for the following Data CREATE TABLE TestTable(Col1 VARCHAR2(20)); insert into TestTable values(&#8216;A11&#8217;); insert into TestTable values(&#8216;A260&#8217;); insert into TestTable values(&#8216;A10&#8217;); insert into TestTable values(&#8216;A5&#8217;); A11 A260 A10 A5 SELECT * FROM TestTable ORDER BY col1; Output A10 A11 A260 A5 You may expected A5 before&hellip; <a href=\"https:\/\/codethataint.com\/blog\/oracle-sorting-alphanumeric-values\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30],"tags":[],"class_list":["post-1114","post","type-post","status-publish","format-standard","hentry","category-queries-oracle-database-2"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/1114","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/comments?post=1114"}],"version-history":[{"count":1,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/1114\/revisions"}],"predecessor-version":[{"id":1115,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/1114\/revisions\/1115"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=1114"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=1114"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=1114"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}