Re: LIKE with COLLATION and ESCAPE

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