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.