HSQLDB is case sensitive!
May 21, 08 by the programmerHmm,
This is not good, i just found out that the HSQLDB database is case sensitive by default
This means that if you have the following strings stored in two rows in a database table
“minanov com is a blog for programmers” and
“MINANOV.COM IS A BLOG FOR PROGRAMMERS”
and you run the following SQL:
SELECT
stored_text
FROM
my_table
WHERE
stored_text = ‘minanov com is a blog for programmers’
will return only one record, the lower case one “minanov com is a blog for programmers“.
We can solve this in couple of ways:
Solution 1:
This solution can only be applied to newly created databases, because the option must be set before a database is created. If you already have a database it won`t affect the existing data. You have to execute the following command before creating the tables.
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, you 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.
Solution 2:
HSQLDB can be extended with a JAVA function.
First you create a public function in some package in your application
com.minanov.hsqldb_utils
public static boolean containsMatch(String target, String search) {
return target.toLowerCase().contains(search.toLowerCase());
}
After we create the function we can call it in our SQL statemants
SELECT
column_for_storing_text
FROM
my_table
WHERE
com.minanov.hsqldb_utils.containsMatch(’column_for_storing_text‘, “text_that_we_search”)
Solution 3:
This solution is very simple. All you have to do is before putting the search string in the SQL as a parameter to eighter make it all lower case or all upper case.
After you make the search string lower or upper case you need to lower or upper the column that you search in.
We will make convert both strings to all uppercase letters.
This is how will that look like in an SQL query:
String text_that_we_search = “minanov com is a blog for programmers“;
String text_that_we_search_to_upper_case = text_that_we_search.toUpperCase();
String sql =”
SELECT
stored_text
FROM
my_table
WHERE
UCASE(stored_text) = “+text_that_we_search_to_upper_case;
Since both strings are in upper case we will have no problem and the above sql will return all the resullts.
That’ll folks 
