Re: NLS_COMP, NLS_SORT, VARCHAR2/NVARCHAR2

On Mar 24, 7:39 am, stvchi..._at_gmail.com wrote:
> Hi,
>
> Lately, we're running into a case-insensitive query issue with
> Oracle 10.2g.
>
> The NLS_COMP & NLS_SORT (for example, setting NLS_COMP to LINGUISTIC
> & NLS_SORT to BINARY_CI) work pretty well with VARCHAR2 columes.
> However, we found out that it didn't have the expected effects on
> NVACHAR2 columns.
>
> Any insight?
>
> Thanks in advance!
>
> - Steve

Since NVARCHAR2s store data in national character set, which is multibyte  AL16UTF16 on 10g by default (and can be UTF-8, which is multibyte,  too,) binary sorts won't work properly on them. You need to use proper linguistic sorts. For example, for German, you could use NLS_SORT=XGERMAN_CI for case-insensitive sort or XGERMAN_AI for both accent-insensitive and case-insensitive sort. You can also try NLS_SORT of GENERIC_M_CI or GENERIC_M_AI (GENERIC_M is multilingual sort for Latin-based alphabets and it uses Unicode canonical equivalence rules.) See the Globalization Support Guide for supported linguistic sorts and choose those appropriate for your data. Note that all linguistic sorts support _CI and _AI postfixes for caseinsensitive  and accent-insensitive sorts.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Sat Mar 24 2007 - 05:25:30 CDT