You made me re-run (and extend) the full test. (DB is AL32UTF8 ,
AL16UTF16 )
Of course you are right!
* First I tested without any NLS_SORT or NLS_COMP parameters set.
(they are default BINARY, BINARY)
The result is as expected, with Predicate Information
1 - filter("NAME" LIKE '\_%' ESCAPE '\')
* the next test is the original one: (*GERMAN_AI*, LINGUISTIC)
*unexpected* result, Predicate:
1 - filter("NAME" LIKE '\_%' ESCAPE )
* with your suggested *BINARY_AI*, LINGUISTIC
expected result, Predicate
1 - filter(NLSSORT("NAME",'nls_sort=''BINARY_AI''')>=HEXTORAW('5F00')
AND
NLSSORT("NAME",'nls_sort=''BINARY_AI''')<HEXTORAW('6000'))
* with case sensitive *GERMAN_CI*, LINGUISTIC
expected result, Predicate
1 - filter("NAME" LIKE '\_%' ESCAPE '\')
I also did some crosschecks with FRENCH_AI and VIETNAMESE_AI.
Any <language>_AI I tested gives *unexpected* result and the *'\'* ESCAPE
just vanishes in the Predicate.
For me it seems to be the combination of any language with AI. (binary, or
CI does not show the strange behavior)
(I also played with NLSSORT("NAME",'nls_sort=''GERMAN_AI'''), but this
raised more questions than answers and extends this thread)
Am Mi., 3. März 2021 um 20:54 Uhr schrieb Jonathan Lewis <
jlewisoracle@xxxxxxxxx>:
I've just copied the test script again and run it from scratch
I get one difference as I switch from german_ai to binary_ai, and that's
with:
select * from t_object where name like '\_%' escape '\';
German_ai -->
NAME
------------
\A
1 row selected.
binary_ai -->
NAME
------------
_A
_??
_B
_
4 rows selected.
The ?? is presumably because my terminal setting doesn't allow the Ä to
travel from client to server.
For reference these are my server character settings.
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
Regards
Jonathan Lewis
On Wed, 3 Mar 2021 at 18:44, Martin Berger <martin.a.berger@xxxxxxxxx>
wrote:
alter session set nls_sort=binary_ai ;
does not change anything.
If I get substantial updates from Oracle I'll post it here.
regards,
Martin
