PanacheQuery: Operators to allow Case Insensitive queries

user_9838946 0
Viewed 701

I am trying to create a query which has the following 2 criteria:

  1. Should be partial string match instead of an exact match
  2. Should be case insensitive

My query is currently like this:

return list("company=?1 and name?=2", company, name);

However this doesn't do partial string match. Is there an operator for this?

Just for clarity's sake, assuming the DB contains peter, Peter, peTeR, a query for name=peter should return all 3 names.

quarkus-panache
1 Answers

You can partially match strings using the LIKE operator combined with the CONCAT function like this:

company like concat('%', ?1, '%')

You can match strings case-insensitively using the LOWER function like this:

lower(name) = lower(?2)

You can also combine both to match the company and the name partially and case-insensitively:

return list("lower(company) like concat('%', lower(?1), '%') and lower(name) like concat('%', lower(?2), '%')", company, name);
user_105867270
Related