Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
and if you try nls_sort=BINARY? –  Munchi Oct 22 '13 at 11:44
    
Thanks, this is a good step. It seems i missed this in the docs. However, Special letters like É or À are now sent to the bottom too (big char code i guess). Hopefully there are not many words concerned in our application. Thank you –  niahoo Oct 22 '13 at 14:14
add comment

2 Answers

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.

share|improve this answer
    
Thank you, but with this,<Non renseigné> is treated like Nonreseigne so it is not sent to the bottom but still mixed with the values starting with N. –  niahoo Oct 22 '13 at 14:17
    
Ok, thanks this was really useful. –  niahoo Oct 22 '13 at 14:23
add comment
up vote 1 down vote accepted

Ok so with the help of Munchi and kevinsky i mixed up their advice to arrive to this :

SELECT LIBELLE as LibelleCourant,
       CODE as code
FROM S_MARQST
ORDER BY NLSSORT(translate(libelle,'ÉéÀàÇçÈèÙù','EeAaCcEeUu'), 'nls_sort=BINARY');

So the sort BINARY sends values starting with < to the top (i would rather the bottom but eh .. it's ok) but before, special letters are translated to normal letters in the order clause.

That's good with French but a little bookkeeping may be necessary for other languages with many more special letters.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.