search person table for a given name

i have a person table in yu db where i store firstname and lastname separately. How could I do a search if firstname was "John" and lastname was "Doe" and I se开发者_运维知识库arched for "John D"?

select * from table where concat_ws(' ',name,surname) like 'John D%'

SELECT * 
FROM table 
WHERE LOWER(CONCAT(lastname, ' ', firstname)) LIKE 'john d%'
   OR LOWER(CONCAT(firstname, ' ', lastname)) LIKE 'john d%'

You should also ensure that you convert all your strings to lower case or do case insensitive search.

Edit: if firstname or lastname can be null, then it will be better to use CONCAT_WS instead of CONCAT.

Split "John D" up into "John" and "D" and then use a query like:

SELECT * FROM person WHERE firstname="John" AND lastname LIKE "D%"