Re: NLS_COMP, NLS_SORT, VARCHAR2/NVARCHAR2

  Thanks a lot, will try!

On 24 Mar 2007 03:25:30 -0700, "Vladimir M. Zakharychev" <vladimir.zakharychev_at_gmail.com> wrote:

>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 multi-
>byte AL16UTF16 on 10g by default (and can be UTF-8, which is multi-
>byte, 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 case-
>insensitive 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 - 11:44:29 CDT