Is ILIKE supported?

[Paul Kippes]

In my version of sqlalchemy I didn't find any ability for ILIKE. Is
this supported?

If a patch is needed, would a new operator "ilike" be okay?

[Paul Kippes]

Maybe I'm not understanding this. Is ILIKE only a mysql thing? So is
LIKE normally case insensitive in complaint SQL database?

[Paul Johnston]

Hi,

>Maybe I'm not understanding this. Is ILIKE only a mysql thing? So is
>LIKE normally case insensitive in complaint SQL database?
>
>

ILIKE is quasi-standard. Postgres has it as well, but MSSQL doesn't -
all LIKEs (and in equals too in fact) are case insensitive.

Paul

[Andreas Kostyrka]

Normally, like is case sensitive.
if you want to query something case insensitive, one can use all kinds
of expressions, one that comes to mind would be
lower(col) like 'lowercase%'

creating indexes on lower(col) would speed that up.

Andreas

* Paul Kippes <paul.want...@gmail.com> [070404 20:07]:

[Andreas Kostyrka]

* Paul Johnston <p...@pajhome.org.uk> [070404 20:11]:

Wrong, the postgresql docs say it exactly the other way:

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.

http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-LIKE

Andreas

[Michael Bayer]

theres a whole bunch of crap regarding case sensitivity in compares.
at this point ive been asked about three thousand times for ILIKE so
sure, we can add it...if we are adding a Like() construct, with a
visit_like() in compiler, thats great; DB's that dont have ILIKE can
pull out lower(x) LIKE(lower(y)) instead. have ilike() produce a
_Like object with "case_sensitive=False".

there is also a general notion of "case sensitive compare"/"case
insensitive compare". we have a rather involved way to solve this
which is talked about in ticket #487, including a PG patch. the
ultimate ability is to put a flag on Column
"case_insensitive_compares" that would produce a DB-specific "case
insensitive" comparison operation automatically. but complicating
matters is MySQL where compares are *already* case insensitive unless
you play around with their non-SQL COLLATE expression. something
that should be looked about/thought about with regards to this matter.

[Paul Kippes]

Michael, I certainly understand why this hasn't been added. With some
databases like sqlite, a case sensitive search isn't even possible.
This is much more complicated that I would have imagined.