sql like query fullname from three columns

Question

I have a textfield which takes in full name example: michael peter johnson in my table i have three columns firstName , middlename and lastname I have written this query but it returns empty

SELECT firstName,middleName,lastName
FROM staff
WHERE firstName LIKE "%michael peter johnson%"
OR middleName LIKE "%michael peter johnson%"
OR lastName LIKE "%michael peter johnson%" 

if a user types in son it should display michael peter johnson, since son is contained in johnson.

thanks my table : firstName has (michael) middleName has (peter) lastName (johnson)

Answer 1

If I understand well, it currently works for parts of firstname/middlename/lastname, but not if you want to enter the full name.

To fix that, yOu can use concat, so it will always work:

select firstName,middleName,lastName 
from staff 
where concat(firstName, ' ', middlename, ' ', lastname) Like "%michael peter johnson%"

Answer 2

You're checking for any string that includes all of michael peter johnson in it.

You need three separate statements, with different comparisons...

WHERE
   firstName  Like '%michael%'
or middleName Like '%peter%'
or lastName   Like '%johnson%"

You could reverse the logic...

WHERE
   'michael peter johnson' LIKE '%' + firstName  +'%'
or 'michael peter johnson' LIKE '%' + middleName +'%'
or 'michael peter johnson' LIKE '%' + lastName   +'%'

You also give an example where the search is for 'son'. Your existing code will work for that. It's just a 'problem' when you compare each individual part of the name against a parameter that has all of the name.

So, maybe you want both version together?

WHERE
   'michael peter johnson' LIKE '%' + firstName  +'%'
or 'michael peter johnson' LIKE '%' + middleName +'%'
or 'michael peter johnson' LIKE '%' + lastName   +'%'
or firstName  LIKE '%' + 'michael peter johnson' + '%'
or middelName LIKE '%' + 'michael peter johnson' + '%'
or lastName   LIKE '%' + 'michael peter johnson' + '%'

You just need to decide exactly what behaviour you want, and then work it through.