How to make a SELECT col1,col2 FROM table WHERE col1+" "+col2 LIKE "John D%"?

Question

I'm trying to select the name and lastname of all users which name+lastname starts with "John D", I mean it could select "John Doe", "John Deniro", "John Dalas".

but this select doesn't work:

SELECT name,lastname FROM users WHERE name+" "+lastname LIKE 'john D%';

So, how can I make that work?

Answer 1

You could use CONCAT

SELECT name,lastname FROM users 
WHERE CONCAT(name , ' ', lastname) LIKE 'John D%';

or (better solution because conditions will be SARGable):

SELECT name,lastname FROM users 
WHERE name = 'John'
  and lastname LIKE 'D%'

Answer 2

You should split the parameters to get it working correctly.

SELECT `name`, `lastname`
FROM `users`
WHERE `name` LIKE "John%" AND `lastname` LIKE "D%"

Answer 3

Or, if performance is vaguely important...

SELECT name
     , lastname 
  FROM users 
 WHERE name = 'John'
   AND lastname LIKE 'D%';