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

Sonar Gerrit Plugin Release

I am happy to announce a first release of my Sonar Gerrit plugin. This plugin reports Sonar violations on your patchsets to your Gerrit server. Sonar analyses full project, but only files included in patchset are commented on Gerrit. Please forward to project page for installation instructions. This plugin is intended to use with Gerrit Trigger plugin for Jenkins CI server. Together they provide a great tool for automatic static code analysis.I am happy to announce a first release of my Sonar Gerrit plugin. This plugin reports Sonar violations on your patchsets to your Gerrit server. Sonar analyses full project, but only files included in patchset are commented on Gerrit. Please forward to project page for installation instructions. This plugin is intended to use with Gerrit Trigger plugin for Jenkins CI server. Together they provide a great tool for automatic static code analysis.