13

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
Manfred Moser
  • 29,539
  • 13
  • 92
  • 123
felarof99
  • 185
  • 1
  • 1
  • 9

2 Answers2

23

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');
Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
13

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
Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152