HSQLDB Tip: Case Insensitive LIKE Query in HSQLDB RDBMS & Space Crunched String Comparison

I previously discussed how to extend HSQLDB relational database with simple Java functions. The example there actually implements case insensitive like query functionality for HSQLDB. Read the article to get an understanding of how to write such custom functions and how to use them. Then read below to find a solution for performing space crunched comparison of strings.

Space crunching of Strings is removing all spaces from a String, not just from the beginning and end as is done in trim(). This is often required when comparing business data from various sources. Here is a Java function to space crunch any String.

public static String removeSpace(String target) {
    if(target == null) return null;

    return target.replace(" ", "");
}

You can use this function from HSQLDB RDBMS like this:

select * from table_name where "com.taragana.Util.removeSpace"("column_name") = 'column_value'


Note: The column_value passed should also be processed by removeSpace().
Note: Remember to escape the " with backslash when embedding in a Java String from a program.

The alternative to this is really painful. You will have to create a temporary table and process the data in code. Yet another reason to use HSQLDB? I think it is the best and simplest low footprint super fast relational database management system. The strong Java integration is a bonus. Can anyone point me how to do the same thing in Oracle?