How to enable case-insensitive search in AdHoc filters (Oracle DB)?

Case insensitive search for Oracle 10g/11g

By default Oracle DB performs case-sensitive search when doing like %char%. That causes filters created in AdHoc Designer to be also case-sensitive. Starting from db version 10g, you can use REGEXP_LIKE condition to enable case-sensitive search. You would need to:

  • Open /WEB-INF/applicationContext-semanticLayer.xml
  • Find:

    <bean id="oracleSQLGenerator" parent="defaultSQLGenerator" scope="prototype" />
  • Edit entry "contains" to use regexp_like(source_char, pattern, match_param). See example below
  • Save this file and restart the server.

Example

<entry key="contains">
    <value>
        def search = args[1].value;
        if (search == null)
        return sqlArgs[0] + " like '%' || " + sqlArgs[1] + " || '%'"
        if (! (search instanceof String)) { search = search.value }
        return " regexp_like(" + sqlArgs[0] + ","  + "'" + search.replace("'","''") + "', 'i')"
    </value>
</entry>

Note this line:

return " regexp_like(" + sqlArgs[0] + "," + "'" + search.replace("'","''") + "', 'i')"

Original line would use LIKE %...%:

return sqlArgs[0] + " like '%" + search.replace("'","''") + "%'" 

Other DBs

Depending on your DB, you may want to customise other related beans in applicationContext-semanticLayer.xml. MySQL by default performs case-insensitive search via collation table. PostgreSQL by default performs case-sensitive search, but can use regular expression in a similar to Oracle manner. See beans "postgreSQLGenerator" and "mysqlSQLGenerator" for details on MySQL or PostgreSQL. You can also find some other, less often used SQL variations defined there.

Feedback
randomness