4.5.3.2.4. Case-Insensitive Search for a Substring

It is possible to use a special feature of JPQL queries execution in datasources, described for the Query interface of the Middleware level: for easy creation of case-insensitive search condition of any substring, (?i) prefix can be used. However, due to the fact that the query value is usually passed implicitly, the following differences take place:

  • The (?i) prefix should be specified before a parameter name and not inside the value.

  • The parameter value will be automatically converted to lowercase.

  • If the parameter value does not have % characters, they will be added to the beginning and the end.

Below is an example of how to process the following query:

select c from sales$Customer c where c.name like :(?i)component$customerNameField

In this case, the parameter value taken from the customerNameField component will be converted to lowercase and will be framed with % characters, and then an SQL query with a lower(C.NAME) like ? condition will be executed in the database.

Please note that with this search, an index created in the DB by the NAME field, will not be used.