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.

SET IGNORECASE

SET IGNORECASE { TRUE | FALSE };

· 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.shtmlHSQLDB,Database,Java