Case-Insensitive Substring Search

You can use the (?i) prefix in the value of the query parameters to conveniently specify conditions for case insensitive search by any part of the string. For example, look at the query:

select c from sales$Customer c where c.name like :name

If you pass the string (?i)%doe% as a value of the name parameter, the search will return John Doe, if such record exists in the database, even though the case of symbols is different. This will happen because ORM will run the SQL query with the condition lower(C.NAME) like ?.

It should be kept in mind that such search will not use index on the name field, even if such exists in the database.