18.104.22.168.4. Case-Insensitive Search for a Substring
This is a legacy API. For new data API available since release 7.0, see Data Components.
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:
(?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.