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'

)
)
ORDER BY NLSSORT(COLUMN_VALUE,'NLS_LANG=pl');

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

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.