[Feature Request] Add ILIKE operator

[negezor]

Motivation

https://www.postgresql.org/docs/14/functions-matching.html#FUNCTIONS-LIKE

Proposed Solutions

Expr::col(...).ilike("%pattern%")
// Or
LikeExpr::str("%pattern%").ilike()
// Or
LikeExpr::str("%pattern%").case_insensitive()

Additional Information

This is of course not a standard statement in SQL. But very commonly used in Postgres.

[ikrivosheev]

@negezor thank you for the issue! Do you want to create PR?)

[billy1624]

Hey @negezor, welcome!! Would you mind sharing your use case? Just show us the resulting SQL :)

Expr::col(...).ilike("%pattern%")

This is already supported in SeaQuery. See https://docs.rs/sea-query/0.26.3/sea_query/expr/struct.Expr.html#method.like

[negezor]

@ikrivosheev I'm not so well versed in the internals of the module yet 😅

@tyt2y3 & @billy1624 Difference between LIKE and ILIKE in case sensitivity.

-- Postgres
SELECT * FROM cakes WHERE name ILIKE '%cHocOlate%'

-- MySQL
SELECT * FROM cakes WHERE LOWER(name) LIKE LOWER('%cHocOlate%')

[billy1624]

Oh, sorry I misunderstand it. I thought it's LIKE but in fact it's ILIKE.

We already support this generic form. i.e. applying LOWER function on a column value then operate LIKE on it. Given the pattern already in lower case.

SELECT * FROM cakes WHERE LOWER(name) LIKE '%lower_case%'

[ikrivosheev]

@tyt2y3 @billy1624 Well, shall we add or is it too specific for postgres?

[billy1624]

I think it's okay to add ILIKE operator. By default the collation of Postgres is case-sensitive whereas MySQL and SQLite are case-insensitive. That means we can fallback to ordinary LIKE operator on MySQL and SQLite while keeping the behaviour the same - search with a case-insensitive pattern.

[hut8]

@billy1624 ~~Hey Billy, I am not sure that SQLite is case-insensitive. https://www.sqlite.org/datatype3.html#collation The default is "BINARY" -- to get case-insensitive you need "NOCASE"~~. I arrived at this issue looking for how to do this with sea-query and couldn't find it :)

Edit: I am totally wrong; although regular comparison like = and < is case-sensitive by default, LIKE is case-insensitive. You were absolutely right.

[ikrivosheev]

Hello! I created PR: https://github.com/SeaQL/sea-query/pull/473