Case insensitive LIKE query
2,002 views
Skip to first unread message
nafg
Aug 17, 2009, 1:48:03 PM8/17/09
to H2 Database
How can I do a case insensitive LIKE query? I need to search any field
if it contains certain text.
if it contains certain text.
Sam Van Oort
Aug 17, 2009, 7:51:02 PM8/17/09
to H2 Database
use "WHERE LOWER(column) LIKE ..."
Victor
Aug 18, 2009, 4:54:30 AM8/18/09
to H2 Database
Set IGNORECASE=TRUE before table creation.
Wes Clark
Aug 18, 2009, 1:16:59 PM8/18/09
to H2 Database
How about setting the collation to something with a PRIMARY strength
before creating any objects in the database?
before creating any objects in the database?
Thomas Mueller
Aug 19, 2009, 2:37:13 PM8/19/09
to h2-da...@googlegroups.com
Hi,
The most portable solution is probably: LOWER(x) LIKE
LOWER('pattern%') (or UPPER). Another solution is: CAST(x AS
VARCHAR_IGNORECASE) LIKE 'pattern%', or create the column using
VARCHAR_IGNORECASE, or call IGNORECASE=TRUE, or use a case insensitive
collation (SET COLLATION, however collations are slow).
> any field
Maybe you want to use fulltext index? H2 supports that.
Regards,
Thomas
The most portable solution is probably: LOWER(x) LIKE
LOWER('pattern%') (or UPPER). Another solution is: CAST(x AS
VARCHAR_IGNORECASE) LIKE 'pattern%', or create the column using
VARCHAR_IGNORECASE, or call IGNORECASE=TRUE, or use a case insensitive
collation (SET COLLATION, however collations are slow).
> any field
Maybe you want to use fulltext index? H2 supports that.
Regards,
Thomas
Tonke Hanebuth
Jan 12, 2018, 10:01:21 AM1/12/18
to H2 Database
Dear nafg,
you may use ILIKE.
"ILIKE does a case-insensitive compare." (http://www.h2database.com/html/grammar.html)
Best regards,
Tonke
Tonke Hanebuth
Jan 12, 2018, 10:10:05 AM1/12/18
to H2 Database
Requirement for this operator is H2 version 1.4.194 (2017-03-10).
Reply all
Reply to author
Forward
0 new messages