Compare value with concatenated fields in where clause

Question

Say I want to search for a user, 'Richard Best'. Is it possible to compare the full name is concatenated first name 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

These 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 might also work:

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