{"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: \u2026 b, c, \u0107, d, e, \u0119, f \u2026 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\u2019t work.<\/p>\n<p>If we sort the following way:<\/p>\n<p>SELECT * FROM TABLE (SYS.ODCIVARCHAR2LIST(\u2018cde\u2019, \u2018\u0107a\u2019, \u2018dx\u2019, \u2018ca\u2019)) ORDER BY COLUMN_VALUE;<\/p>\n<p>We\u2019ll get: ca, cde, dx, \u0107a and that\u2019s wrong. To sort the list correctly, we can specify a language rule: NLSSORT(COLUMN_VALUE,\u2019NLS_LANG=pl\u2019):<\/p>\n<p>SELECT * FROM TABLE (SYS.ODCIVARCHAR2LIST(\u2018cde\u2019, \u2018\u0107a\u2019, \u2018dx\u2019, \u2018ca\u2019)) RDER BY NLSSORT(COLUMN_VALUE,\u2019NLS_LANG=pl\u2019);<\/p>\n<p>Finally we\u2019ll 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}]}}