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

GWT Designer for Eclipse 3.6 can cause project compile freeze

Lately I installed GWT Designer for Eclipse Helios (3.6). I wanted to check it's features. They aren't so cool I've expected but that's other story. The problem was that suddenly my main GWT enabled project began to freeze during compilation.  The...Lately I installed GWT Designer for Eclipse Helios (3.6). I wanted to check it's features. They aren't so cool I've expected but that's other story. The problem was that suddenly my main GWT enabled project began to freeze during compilation.  The...