I have a search field that can take values separated by blank spaces for multi-value search. But now I want to make it a multi-value search using wildcards. For example, if I add 'away%' and 'ds%' separated by a blank space in the search field it should search for both wild card values in a given column. The query should be as :
SELECT * FROM [table]
WHERE [column] LIKE ('away%') OR
[column] LIKE ('ds%');
How can I achieve this repetitive column reference based on the number of search values? P.S: search value can be of any number.
Here is an example of how to do that:
Mapper Class:
Mapper XML:
Notes:
<if>
Checks for a null parameter. In that case, noWHERE
clause will be added.<foreach>
Splits the parameter using the\s+
regex and then iterates over the results adding each term in a separatename LIKE
clause and joining them byOR
.CONCAT(CONCAT('%', #{item}), '%')
Is written in this way to be used with Oracle database (takes two parameters in Oracle). It can be different in other database systems. For example,CONCAT('%', #{item}, '%')
can be used in MySQL.