Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Finnish sorting feature FINNISH_FINLAND.WE8ISO8859P1
On Fri, 20 Mar 1998 08:30:02 GMT, mcw_at_microlan.se (McW) wrote:
>Hello,
>
>NLS_LANG = FINNISH_FINLAND.WE8ISO8859P1
>
>Select statements with order by on a varchar column returns data sorted like:
>
>Duke Nukem
>Frodo
>Gandalf
>Lara Croft
>0
>1
>2
>3
>
>Our customer would love to have the alphanums at the beginning
>which can be acquired by setting NLS_LANG or NLS_SORT to
>anything except FINNISH_FINLAND.
>Anyone having a Finnish Oracle experience?
>Is it a feature or a bug?
>
Its not so only with FINNISH, it's the same with most other European
languages (and I belive with non-european also). It's the same with
FRENCH, GERMAN, SLOVENIAN, WEST_EUROPEAN,....
If you want to order by their natural ASCII number representation, you
must set NLS_SORT parameter to BINARY (either on instance level by
seting it in your 'init<SID>.ora' or on session level by isuing
'ALTER SESSION SET NLS_SORT=BINARY;')
or use function NLSSORT (e.g.
'SELECT ...... ORDER BY NLSSORT(colx,'NLS_SORT=BINARY');').
In both cases your query will not be able to use index on your ordering column, it will perform full table scan, and it most probably won't sort correctly regarding any special Finnish characters.
Note also, that if you are using linguistic sorting sequences the language special characters will be sorted correctly in the ORDER BY clause, but in WHERE clause they are evaluated by their binary value and hence they will not be sorted correctly. For example, with your data sample, the following query:
'SELECT colx FROM xtable WHERE colx > 'Gandalf';' will return only the row with "Lara Croft" and not also all rows with number string values as one could expect regarding to the output of the ORDER BY query.
All this is explaind in detail in the Oracle7 Server Refference guide.
>Thanks
>Mats
Regards,
Jurij Modic Republic of Slovenia jurij.modic_at_mf.sigov.mail.si Ministry of Finance ============================================================The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Mar 20 1998 - 00:00:00 CST