How to configure Presto searches to be case-insensitive?

Question

In my case, Presto connects to a MySQL database which has been configured to be case-insensitive. But any search through Presto seems to be case-sensitive.

Questions:

1) Is there a way to configure Presto searches to be case-insensitive? If not, can something be changed in the Presto-MySQL connector to make the searches case-insensitive?

2) If underlying DB is case-insensitive, shouldn't Presto searches also be case-insensitive? (I presume that Presto only generates the query plan and the actual execution happens on the underlying database)

Example: Consider the below table on MySQL.

name
____
adam

Alan


select * from table where name like '%a%'
// returns adam, Alan on MySQL
// returns only adam on Presto

select * from table where name = 'Adam'
// returns adam on MySQL
// returns NIL on Presto

Tyner

What does this have to do with facebook? Tag removed. – Durward

Answer 1

You have to explicitly ask for case-insensitive comparison by normalizing compared values either to-lower, or to-upper, like this:

select * from table where lower(name) like '%a%';

select * from table where lower(name) = lower('Adam');

Orville

Answer 2

You can use regexp_like(), and prepend the regexp with (?i) for case insensitivity

select 
  * 
from table_name 
where 
  regexp_like(column_name, '(?i)fOO');  -- column contains fOO or FOO

or

select 
  * 
from table_name 
where 
  regexp_like(column_name, '(?i)^Foo'); -- column starts with fOO or FOO

Attrition

This answer should be higher up! – Gehrke