Re: LIKE with COLLATION and ESCAPE

It does appear that the missing backslash for escape is cause.

Ran Martin's LiveSQL script and then inserted this row:

INSERT INTO "T_OBJECT" (NAME) VALUES ('\A'); Query finds it (and only it):

select * from t_object where name like '\_%' escape '\';

[image: image.png]

But the escape character only seems to disappear when % is used?

It's reproducible with other escape characters, not just \

Bug...

Cheerio
/Kim

On Mon, Mar 1, 2021 at 11:53 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> Some interesting results from:
>
> select name, dump(name,16) dmp, nlssort(name, 'nls_sort=''GERMAN_AI''')
> nlss, dump(nlssort(name, 'nls_sort=''GERMAN_AI'''),16) from t_object order
> by 3
>
> especially after inserting 'ÄÄ' and 'ÄÄÄ'
>
>
> Also (possibly more significant):
>
> explain plan for select * from t_object where name like '\_%' escape '\';
> select * from table(dbms_xplan.display);
>
> The Predicate information is odd (at least on my machine, but that may be
> due to differences in O/S linguistic setup).
> I get the following predicate:
>
> 1 - filter("NAME" LIKE '\_%' ESCAPE )
>
> The '\' seems to have disappeared.
>
>
>
> 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
>>
>

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




image/png attachment: image.png