在WHERE子句中组合两个字符串记录

Question

I have two columns name and surname. I would like to make a search query which will find the person if the user writes either name, surname or both.

Currently I have

    SELECT name, surname, id FROM users WHERE 
    (name LIKE '%".$term."%' || surname LIKE
    '%".$term."%')  AND position='0' LIMIT 5

Let's say I have a user John Lemon. The query will find him if I write John or I write Lemon. But it will find nothing if I write John Lemon.

Is it possible to have a syntax similar to:

 SELECT name, surname, id FROM users WHERE (
  name+' '+surname LIKE
 '%".$term."%')  AND position='0' LIMIT 5

Answer 1

Use this query:

SELECT name, surname, id FROM users WHERE
CONCAT(name, ' ', surname) LIKE '%".$term."%'