Re: LIKE with COLLATION and ESCAPE

Thank you Jonathan,

In the meantime I opened a SR at Oracle (SR 3-25336436801 - in case someone wants to give the poor support analyst a hand and show him how to use a testcase on liveql).

You are right, the "alter session set default_collocation" is not required. It's an artefact from some other tests I did with a table   CREATE TABLE "A"."T_OBJECT2"
   ( "NAME" VARCHAR2(12)
*COLLATE "GERMAN_AI"* )
this requires max_string_size = extended. As it did not help in my tests, I removed the table and it's tests, just forgot about the default_collocation.

Bug 10224414 and some others looks similar. But all those I found are "fixed in" 12. or older. That's the reason I did not dig into them deeper.

I did not mention it yet, my sandbox is NLS_CHARACTERSET AL32UTF8. So nothing special here.
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

Am Mi., 3. März 2021 um 11:36 Uhr schrieb Jonathan Lewis < jlewisoracle_at_gmail.com>:

>
> A couple of follow-ups.
> a) I've only just realised that the "alter session set default_collation"
> was complaining that I didn't have max_string_size = extended. (Didn't make
> any difference, though, but it's an interesting point, and one to beware
> of, that it is set on LiveSQL).
>
> b) This looke remarkably like bug 10224414 (which is reported fixed in
> 12.0). Workaround "Avoid using the ESCAPE clause". (You could take a leaf
> from Oracle's book on that one - "select * from t_object where
> translate(name,'_','#') like '#%' order by d1; " - which they've used in
> the definition of gv$parameter since ca. Oracle 6.
>
> c) This may depend on your character set, but do you get the results
> you're expecting if you specify binary_ai rather than german_ai ?
>
> Regards
> Jonathan Lewis
>
>
>
> On Fri, 26 Feb 2021 at 19:35, Martin Berger <martin.a.berger_at_gmail.com>
> wrote:
>
>> Hi List,
>>
>> Yesterday I was confronted with an interesting question they faced.
>> They have some entries in a varchar2 column such as
>> _A, _B, _Ä, _, A
>> The LIKE pattern is '\_%' ESCAPE '\'
>> when NLS_COMP is LINGUISTIC and NLS_SORT is set to GERMAN_AI
>> this LIKE doesn't return any rows.
>>
>> My suggested workaround is to use
>> ('\_%' COLLATE GERMAN_AI ) escape '\'
>> But I can't explain, why this is required to make the % work as I expect.
>> Why % is sensitive of the collation?
>>
>> Can anyone give me a hint, please?
>>
>> A simple testcase can be found at
>> https://livesql.oracle.com/apex/livesql/s/lfcf58k7cj18t22jeth3rz6cu
>>
>> thank you,
>> Martin
>>
>> --
>> Martin Berger Oracle ♠
>> martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
>> ^∆x http://berxblog.blogspot.com
>>
>

-- 
Martin Berger                Oracle ♠
martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
^∆x      http://berxblog.blogspot.com

--
http://www.freelists.org/webpage/oracle-l