How to perform SELECT query on concatenated columns in RoomDB?

Question

I have a table(user) with the structure

+-----+----------+------------+
|ID   |firstName |lastName    |
+-----+----------+------------+
|1    |John      |Wesley      |
+-----+----------+------------+
|2    |Ashley    |Copper      |
+-----+----------+------------+

Search query 'n W' should results 'John Wesley' and 'y C' should results 'Ashley Copper' out fo the table.

Answer 1

Please try this:-

Select firstName,lastName from user where (firstName like "%y" and lastName like "C%") or (firstName like "%n" and lastName like "W%")

Answer 2

Finally got the answer,

SELECT * FROM User WHERE ((firstName||" "||lastName)  LIKE %query%)

Answer 3

Use the following query on your Room Dao method -

SELECT user.firstName || user.lastName AS 'FULL_NAME' FROM User;