Case insensitive search for Oracle 10g/11gBy 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: | [toc] |
- 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.
Recommended Comments
There are no comments to display.