Using PostgreSQL's ILIKE instead of LIKE.

[Murray Cumming]

jooq's Field has a like() method to create a Condition:
http://www.jooq.org/javadoc/latest/org/jooq/Field.html#like(java.lang.String)

But how can I use the ILIKE operator provided by PostgreSQL to do
case-insensitive matching?:
http://www.postgresql.org/docs/devel/static/functions-matching.html

Or do you suggest some other way to do a simple case-insensitive match
for a sub-string inside a field's text value?

--
mur...@murrayc.com
www.murrayc.com
www.openismus.com

[Sander Plas]

Murray: as a workaround, you can do something like  "lower(val1) LIKE lower(val2)".

Lukas: i too think that support for ILIKE would be a great addition :) Not something with a very high priority though, since there is a workaround available.

BTW - I haven't had time yet to test your change to the code generation schemata stuff yet, unfortunately :(

On Saturday, May 12, 2012 9:49:31 PM UTC+2, Murray Cumming wrote:

jooq's Field has a like() method to create a Condition:
http://www.jooq.org/javadoc/latest/org/jooq/Field.html#like(java.lang.String)

But how can I use the ILIKE operator provided by PostgreSQL to do
case-insensitive matching?:
http://www.postgresql.org/docs/devel/static/functions-matching.html

Or do you suggest some other way to do a simple case-insensitive match
for a sub-string inside a field's text value?

--

www.murrayc.com
www.openismus.com

[Lukas Eder]

Hello Murray, Sander

> Murray: as a workaround, you can do something like "lower(val1) LIKE
> lower(val2)".

Yes, that would be my immediate suggestion too. That's actually how
jOOQ would simulate ILIKE in other database dialects, although I'll
have to read into collations, and whether there is any difference when
doing this comparison with LOWER() or UPPER()

> Lukas: i too think that support for ILIKE would be a great addition :) Not
> something with a very high priority though, since there is a workaround
> available.

I agree with that. It's a simple addition and will be implemented in jOOQ 2.4.0:
https://sourceforge.net/apps/trac/jooq/ticket/1423

I personally prefer not to use the Postgres "wording" of this
operator, as jOOQ already features equalIgnoreCase() (similar to
java.lang.String.equalsIgnoreCase()) and notEqualIgnoreCase(). In some
future release, I might also add containsIgnoreCase(),
startsWithIgnoreCase() and endsWithIgnoreCase(). Since most dialects
will have to simulate this operator, ILIKE might not be less
intuitive, even if it is also less verbose.

What do you think?

> BTW - I haven't had time yet to test your change to the code generation
> schemata stuff yet, unfortunately :(

No worries. Let's hope it works anyway :-)

Cheers
Lukas

2012/5/13 Sander Plas <sande...@gmail.com>:

[Murray Cumming]

On Sun, 2012-05-13 at 20:09 +0200, Lukas Eder wrote:
> > Murray: as a workaround, you can do something like "lower(val1)
> LIKE
> > lower(val2)".

That's working well, thanks.

>
> Yes, that would be my immediate suggestion too. That's actually how
> jOOQ would simulate ILIKE in other database dialects, although I'll
> have to read into collations, and whether there is any difference when
> doing this comparison with LOWER() or UPPER()
>
> > Lukas: i too think that support for ILIKE would be a great
> addition :) Not
> > something with a very high priority though, since there is a
> workaround
> > available.
>
> I agree with that. It's a simple addition and will be implemented in
> jOOQ 2.4.0:
> https://sourceforge.net/apps/trac/jooq/ticket/1423

Thank you.

--
Murray Cumming
mur...@murrayc.com
www.murrayc.com
www.openismus.com