Re: German characters äöü etc.

Hans Sauren wrote:
> Hi all,
> we use ORACLE 8.1.7. In the tables German characters like äöü have to
> be
> stored. The parameters NLS_LANG is set to
> GERMAN_GERMANY.WE8ISO8859P15,
> NLS_SORT to GERMAN and NLS_TERRITORY to GERMANY. In selects without
> where-clause but order by n_name the result is proper sorted like
> German
> where German ü is like u. But when I force select n_name from adress
> where
> n_name >= 'Mü' all other Mu are not in the resultset. Looking at the
> result
> set with SQL*Plus 'Müller' is shown as 'MA¼ller' which might be
> US7ASCII.
>

Hello Hans,
order by uses nls_sort to determine the german sort order. This is also a reason for the fact that order by does not use an index on the ordered column when NLS_SORT is set to anything else than BINARY. (Maybe one can create some clever function based index but that's another story ;-) To solve your problem you can try something like this:

select * from your_table where nlS_sort(n_name)>=nls_sort('Mü');

or maybe introduce an extra sort/search column with expanded umlauts (ü -> ue etc.) and all characters in upper case.

The fact that SQL*Plus does not show these characters may come from invoking SQL*Plus in a DOS box on Windows. The DOS box uses another character set the the rest of Windows. You will have to adjust NLS_LANG for this session. If you do not use Windows, maybe your terminal is not able to dispaly umlauts.

Hope that helps,
Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de