{"id":689,"date":"2011-02-18T16:28:08","date_gmt":"2011-02-18T14:28:08","guid":{"rendered":"http:\/\/touk.pl\/blog\/?p=689"},"modified":"2023-03-23T13:31:59","modified_gmt":"2023-03-23T12:31:59","slug":"sorting-strings-in-oracle-by-national-rules","status":"publish","type":"post","link":"https:\/\/touk.pl\/blog\/2011\/02\/18\/sorting-strings-in-oracle-by-national-rules\/","title":{"rendered":"Sorting strings in Oracle by national rules"},"content":{"rendered":"<p>Sometimes we need to sort a list by rules of a national letter order, e.g. in Polish the national charactes are mostly place between original the Latin ones: &#8230; b, c, \u0107, d, e, \u0119, f &#8230; In other languages, it can be even more sophisticated, like in Spanish, where <em>ll<\/em> is located after <em>lz<\/em>, so a single character replacement wouldn&#8217;t work.<\/p>\n<p>If we sort the following way:<\/p>\n<p>SELECT * FROM TABLE (SYS.ODCIVARCHAR2LIST(&#8216;cde&#8217;, &#8216;\u0107a&#8217;, &#8216;dx&#8217;, &#8216;ca&#8217;)) ORDER BY COLUMN_VALUE;<\/p>\n<p>We&#8217;ll get: ca, cde, dx, \u0107a and that&#8217;s wrong. To sort the list correctly, we can specify a language rule: NLSSORT(COLUMN_VALUE,&#8217;NLS_LANG=pl&#8217;):<\/p>\n<p>SELECT * FROM TABLE (SYS.ODCIVARCHAR2LIST(&#8216;cde&#8217;, &#8216;\u0107a&#8217;, &#8216;dx&#8217;, &#8216;ca&#8217;)) RDER BY NLSSORT(COLUMN_VALUE,&#8217;NLS_LANG=pl&#8217;);<\/p>\n<p>Finally we&#8217;ll get the correct result: ca, cde, \u0107a, dx.<\/p>\n","protected":false},"excerpt":{"rendered":"Sometimes we need to sort a list by rules of a national letter order, e.g. in Polish the&hellip;\n","protected":false},"author":25,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[],"class_list":{"0":"post-689","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-development-design"},"_links":{"self":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/689","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/users\/25"}],"replies":[{"embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/comments?post=689"}],"version-history":[{"count":7,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/689\/revisions"}],"predecessor-version":[{"id":15630,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/689\/revisions\/15630"}],"wp:attachment":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/media?parent=689"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/categories?post=689"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/tags?post=689"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}