Presto: Implement ILIKE predicate

[RosterIn]

Comparing case-insensitive strings can be source for confusion.
we must use lower() on both sides of the query like shown:
https://stackoverflow.com/questions/42850329/how-to-configure-presto-searches-to-be-case-insensitive

Some DBs like PostgreSQL extended to support also ILIKE

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

The operator ~~ is equivalent to LIKE, and ~~ * corresponds to ILIKE. There are also !~~ and !~~* operators that represent NOT LIKE and NOT ILIKE, respectively. All of these operators are PostgreSQL-specific.

https://www.postgresql.org/docs/current/functions-matching.html

This is very useful. I hope this can be added to Presto.

[dain]

I know it isn't the same, but Presto does support case insensitive regular expression matching with (?i) flag. See https://prestosql.io/docs/current/functions/regexp.html for more information.

[RosterIn]

so maybe on the backside the ILIKE can be implemented with regexp ?

[dain]

The existing LIKE is implemented with a regular expression, so if ILIKE is added I would expect the same.

[ebyhr]

Adding the optional keyword might be one of the candidates. This is Teradata syntax to control the case sensitivity.

SELECT empname FROM tbl_emp WHERE empname LIKE '%JO%’;
SELECT empname FROM tbl_emp WHERE empname (CASESPECIFIC) LIKE '%JO%’;

[dain]

We also could just add a function.

[martint]

Yes, a function would be more appropriate than adding dedicated non-standard syntax.

[KevinKobi]

I wonder why PostgreSQL choose ILIKE over a function in this case. They must had a reason

[boristyukin]

it is also ILIKE in Impala and SnowFlake if it is worth something. Came here looking how to do ILIKE in presto :)

[RosterIn]

@martint @ebyhr Does the "syntax needs review" label means that only after someone submit PR you will check if this idea is good or not? If there is no intention of accepting this there is no reason to keep this issue alive.

[ebyhr]

@RosterIn We use syntax needs review label when the syntax isn't yet decided. It doesn't mean PR won't be accepted.