CODEWITHSUNDEEP
sqloracle-databaseescapingcollate
Charlie
Charlie

Reputation: 41

SQL query not returning results when combining collate and escape function using mutated vowels (umlauts)

I have a query just like this:

SELECT DISTINCT name
FROM table
WHERE name COLLATE BINARY_AI LIKE '%ög%' ESCAPE '\';

The expected behavior would be that it returns names containing ög, like:

Högendorf

But, for example, also names containing just og, like:

Vogel

However, executing this query doesn't deliver a single result. For reference, the data type of name is CHAR(30)

If I slightly change it to use '%og%' instead:

SELECT DISTINCT name
FROM table
WHERE name COLLATE BINARY_AI LIKE '%og%' ESCAPE '\';

It suddenly works and delivers the results as expected. It also does so if I keep the '%ög%' but remove the ESCAPE '\' part of the query

SELECT DISTINCT name
FROM table
WHERE name COLLATE BINARY_AI LIKE '%ög%';

If I remove the COLLATE BINARY_AI but leave the ESCAPE '\' instead, it works, but it doesn't find results like Vogel

And if I add a second COLLATE BINARY_AI to the '%ög%', it also doesn't return any results at all.

Why does this happen, and how could I fix this so I could keep the ESCAPE function?

Upvotes: 1

Views: 90

Answers (1)

Chris Saxon
Chris Saxon

Reputation: 9805

Yep, that's not right!

This is fixed in the Oracle Database 23c Free release:

select banner_full from v$version;

BANNER_FULL                                                                                                                                                     
-------------------------------------------------------------------------------
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

create table t (
  c1 varchar2(30)
);
insert into t values ( 'Högendorf' );
insert into t values ( 'Vogel' );

select * from t
where  c1 collate binary_ai like '%ög%' escape '\';

C1                            
------------------------------
Högendorf
Vogel

If you need a fix for this, speak with support.

Upvotes: 2

Related Questions