Compare value with concatenated fields in the where section

Question

Say I want to find the user "Richard Best". Can I compare the full name associated with the first and last name? I do not have a full name field.

select * from users where last_name + ' ' + first_name like '%richa%' 

I am using mysql

Answer 1

They are equivalent:

 select * from users where concat(last_name,' ',first_name) like '%richa%' select * from users where concat_ws(' ',last_name,first_name) like '%richa%' 

This may also work:

 select * from users where last_name like '%richa%' or first_name like '%richa%' 

Answer 2

Take a look at this topic.

Using mysql concat () in a WHERE clause?

Answer 3

 select * from users where (first_name + ' ' + last_name) like '%richa%'