HSQLDB CASE-Insensitive ‘LIKE’ QUERY – THREE Implementation Methods


Method 1: We can use the following command to change the case sensitivity of the text-comparison of any table which we are going to create.

· Disables (ignorecase = true) or enables (ignorecase = false) the case sensitivity of text comparison and indexing for new tables.· By default, character columns in new databases are case sensitive. The sensitivity must be switched before creating tables.· Existing tables and their data are not affected.· When switched on, the data type VARCHAR is set to VARCHAR_IGNORECASE in new tables.· Alternatively, we can specify the VARCHAR_IGNORECASE type for the definition of individual columns. So it is possible to have some columns case sensitive and some not, even in the same table.· Only an administrator may do this.Link: http://hsqldb.org/web/hsqlDocsFrame.html

Method 2: We can extend HSQLDB with JAVA functions.

Java function to do the case-insensitive searchpublic static boolean containsMatch(String target, String search) {return target.toLowerCase().contains(search.toLowerCase());}

  • This function must be a public static function.
  • Same as the Stored Procedures in HSQLDB

Query used to get the dataselect distinct “COLUMN NAME” from Sheet where "com.companyname.xxx.Util.containsMatch"("COLUMN NAME", ‘String to search’)Link: http://blog.taragana.com/index.php/archive/tip-how-to-extend-hsqldb-rdbms-with-java/

Method 3: We can use the methods like LCASE or UCASE to the column name. It’s giving inside the SQL QUERY itself. This will first convert that column values into a SPECIFIC CASE before comparison.

SQL QuerySelect FIELD_NAME from TABLE_NAME WHERE “+ “LCASE (FIELD_NAME) LIKE ‘%” + search_String + “%’We can also use UCASESelect FIELD_NAME from TABLE_NAME WHERE “+ “UCASE (FIELD_NAME) LIKE ‘%” + search_String + “%’Here search_String is the string which we want to search inside that database.Link: http://www.devdaily.com/java/jwarehouse/hsqldb/src/org/hsqldb/sample/FindFile.java.shtml,,


3 thoughts on “HSQLDB CASE-Insensitive ‘LIKE’ QUERY – THREE Implementation Methods

  1. Wiley

    I read a lot of interesting content here. Probably you spend a lot of time writing, i know how to save
    you a lot of time, there is an online tool that creates readable,
    google friendly posts in seconds, just type in google – laranitas free content source

  2. kandangkambing

    nice post ^.^ we used the HSQLDB too..

    just for share our info..

    in java programmning language, there`s a simple way to make a String be lowercase & otherwise.

    String example = “HELLO..”;
    example = example.toLowerCase();

    after that, we can do the query on the HSQLDB as usual.

    We used that way too in our open-source project,
    a portable-dictionary named “KAMBING” on version 1.1.0 (under construction now..)



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s