In my application I’ve saved staff name as first name, middle name & last name separately on the database. So if the user enters the full name or part of the name to search the staff, it should search first name, middle name & last name combinedly. One of the main problem here to address is that some staff only have first and last name and some others have both first, middle, last name and also some only have the first name. How to write a hql query here to sort this situation.

Staff name will be like below :

Rajesh Chauhan,
Sumit T Shekhar,
Gourav,
Sonam Mithra Kiran,
Mahipal H Sharma,
Agasthya Rao, …..

Below is the query that i wrote, but it will not work.

Query query = session.createQuery("FROM Staff where type = :type and (firstName like :keyStr OR middleName like :keyStr OR lastName like :keyStr OR concat(firstName,' ',middleName,' ',lastName) like :keyStr)");
        query.setParameter("type", 1);
        query.setParameter("keyStr", "%" + searchStr + "%");
        dataList = query.list();