Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Finnish sorting feature FINNISH_FINLAND.WE8ISO8859P1

Re: Finnish sorting feature FINNISH_FINLAND.WE8ISO8859P1

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/03/20
Message-ID: <35128134.30399259@www.sigov.si>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US