3.6.2.2.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:
-
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.