Strange Oracle ORDER BY

oracleoracle-10g

I have a problem sorting strings in french :

This is my query :

SELECT LIBELLE as LibelleCourant,
       CODE as code
FROM S_MARQST
ORDER BY LibelleCourant;

And this is the result 

LIBELLECOURANT  | CODE
----------------------
BAYARD          | BAY
LESIER          | LES
<Non renseigné> | INC
PAM             | PAM

As you can see, oracle seem to ignore the '<' charactrer and order only with alphabet characters. <Non renseigné> is neither at the top nor at the bottom. I tried NLSSORT(name, 'NLS_SORT=french'); but the result is the same.

I would like to have a basic ordering, with special chars either at the top or at the bottom (or both if it uses char codes, nevermind). As the data is user-generated, i cannot predict which chars will be used so i think i can not use CASE statements.

Thank you.

PS: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Prod

Best Answer

I use this solution but it is not an elegant solution. It converts all accented french characters to english equivalents and strips out odd characters.

order by translate(LibelleCourant,'ÉéÀàÇçÈèÙù''/*;"<>() ','EeAaCcEeUu')

Edit: if you want to keep the special characters to sort by then change the translate function.
Example: you want to keep the < and > symbols, just add them to the end string order by translate(LibelleCourant,'ÉéÀàÇçÈèÙù<>''/*;"() ','EeAaCcEeUu<>')

What is happening is that any single character which is present in the first string and has a corresponding value in the second string will be replaced. Extra characters will be removed. These characters will be replaced ÉéÀàÇçÈèÙù<> and these characters will be removed: ''/*;"()

See the Oracle docs for more info.

Related Question