RE: LIKE with COLLATION and ESCAPE

Just checking: Some interactive shells used to silently strip off some characters as input and may still.  

Does the intended sql entered persist in your particular sql_text?  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kim Berg Hansen Sent: Monday, March 01, 2021 8:27 AM
To: jlewisoracle_at_gmail.com
Cc: Martin Berger; Oracle-L oracle-l
Subject: 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.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> https://livesql.oracle.com/apex/livesql/s/lfcf58k7cj18t22jeth3rz6cu  

thank you,

 Martin     

-- 

Martin Berger                Oracle ♠

martin.a.berger_at_gmail.com  <https://twitter.com/martinberx> _at_martinberx
^∆x      http://berxblog.blogspot.com










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




image/png attachment: image001.png