Sorting strings in Oracle by national rules

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: … b, c, ć, d, e, ę, f … In other languages, it can be even more sophisticated, like in Spanish, where ll is located after lz, so a single character replacement wouldn’t work.

If we sort the following way:

SELECT * FROM TABLE (SYS.ODCIVARCHAR2LIST(‘cde’, ‘ća’, ‘dx’, ‘ca’)) ORDER BY COLUMN_VALUE;

We’ll get: ca, cde, dx, ća and that’s wrong. To sort the list correctly, we can specify a language rule: NLSSORT(COLUMN_VALUE,’NLS_LANG=pl’):

SELECT * FROM TABLE (SYS.ODCIVARCHAR2LIST(‘cde’, ‘ća’, ‘dx’, ‘ca’)) RDER BY NLSSORT(COLUMN_VALUE,’NLS_LANG=pl’);

Finally we’ll get the correct result: ca, cde, ća, dx.

You May Also Like

4Developers 2010 Review

I've been to 4Developers in 2009 in Cracow, together with Tomasz Przybysz and we had very nice impressions, no wonder then I wanted to signed up for 2010 edition in Poznań as well. Tomasz was sick, but Jakub Kurlenda decided to come with me. This time...I've been to 4Developers in 2009 in Cracow, together with Tomasz Przybysz and we had very nice impressions, no wonder then I wanted to signed up for 2010 edition in Poznań as well. Tomasz was sick, but Jakub Kurlenda decided to come with me. This time...

HISE home page

Apache HISE has recenlty kickstarted and has a home page here: http://incubator.apache.org/hise/index.html.Apache HISE is Human Interactions Service Engine. It's an open source implementation of WS HumanTask specification.HISE proposal can be found her...Apache HISE has recenlty kickstarted and has a home page here: http://incubator.apache.org/hise/index.html.Apache HISE is Human Interactions Service Engine. It's an open source implementation of WS HumanTask specification.HISE proposal can be found her...