Re: LIKE with COLLATION and ESCAPE

Hi, Martin

I can't explain your testcase either - I'd call it a bug?

I've seen somewhat similar quirks before like in Slide 66 of my presentation:
https://www.slideshare.net/KimBergHansen/when-7-bitascii-aint-enough-about-nls-collation-charsets-unicode-and-such-v2/66

But that was only quirks in the way LIKE works with a special linguistic rule of Danish, it didn't influence how the % wildcard operated. Your case is very weird IMHO ;-)

Regards

Kim Berg Hansen
Senior Consultant at Trivadis
Oracle ACE Director

Author of Practical Oracle SQL
<https://www.apress.com/gp/book/9781484256169> http://www.kibeha.dk
kibeha_at_kibeha.dk
_at_kibeha <http://twitter.com/kibeha>

On Fri, Feb 26, 2021 at 8:35 PM 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