How to query for String contains case-insensitive?

[Ed Erwin]

I would like to know the best way to query for a string "contains" another string in a case-insensitive manner, and let JOOQ automatically take care of all escape characters.

JOOQ 3.6 contains predicates for "likeIgnoreCase" and "contains", but not "containsIgnoreCase".

http://www.jooq.org/doc/3.6/manual/sql-building/conditional-expressions/like-predicate/

I can think of at least two logical ways to do this:

1. BOOK.TITLE.lower().contains(myString.toLowerCase());

2. BOOK.TITLE.lower().contains(DSL.lower(myString));

I would suspect that solution number 2 is best because the case conversion for both strings is being done in the SQL code.  In solution 1, the case conversion for one string is in SQL and the other is in Java, and it is possible that the two use different Locales.

I'm pretty sure that this third possibility would be wrong, because myString might contain "%":

3. BOOK.TITLE.likeIgnoreCase("%" + myString + "%");

Any advice?

[Lukas Eder]

2015-06-04 19:12 GMT+02:00 Ed Erwin <enw...@gmail.com>:

I would like to know the best way to query for a string "contains" another string in a case-insensitive manner, and let JOOQ automatically take care of all escape characters.

JOOQ 3.6 contains predicates for "likeIgnoreCase" and "contains", but not "containsIgnoreCase".

Yes, we actually realised that there will be a lot of possible combinations of ignoring case with other like-esque utility methods, which may:

1. Bloat the API

2. Prevent supporting vendor-backed functions, such as "contains()", which might be supported in some databases.

3. Lower-casing values isn't a completely accurate implementation for "ignore case" in all collations / character sets / languages

 

http://www.jooq.org/doc/3.6/manual/sql-building/conditional-expressions/like-predicate/

I can think of at least two logical ways to do this:

1. BOOK.TITLE.lower().contains(myString.toLowerCase());

2. BOOK.TITLE.lower().contains(DSL.lower(myString));

I would suspect that solution number 2 is best because the case conversion for both strings is being done in the SQL code.  In solution 1, the case conversion for one string is in SQL and the other is in Java, and it is possible that the two use different Locales.

I'm pretty sure that this third possibility would be wrong, because myString might contain "%":

3. BOOK.TITLE.likeIgnoreCase("%" + myString + "%");

Any advice?

Go more low-level:

BOOK.TITLE.lower().like(myString.toLowerCase().replace("%", "!%"), '!');

Hope this helps,

Lukas