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

Loops performance in Groovy

IntroductionIn the 2018 Advent of Code challenged I solved all the puzzles in Groovy. It is pretty obvious, that choosing good data structure is the most important to obtain performant solution. However, the way we iterate over those structures is also...