MySQL Search Query on two different fields

mysql

Solution

You need to re-state the concat expression in your where clause.

 SELECT CONCAT(first_name, ' ', last_name) as fullname 
      FROM users 
     WHERE CONCAT(first_name, ' ', last_name) LIKE '%doe%';

Unfortunately "as" just create a column alias, not a variable that you can use elsewhere.

Problem

I need to search on two fields using LIKE function and should match also in reverse order. My table uses InnoDB which dont have Full text search. Consider the following case: I have users table with first_name and last_name column. On it, there is a row with the following value: ``` { first_name: 'Ludwig', last_name: 'van Beethoven', } ``` Cases: - Can search "Ludwig van Beethoven" - Can search "Beethoven Ludwig" - Can search "Ludwig" - Can search "Beethoven" I tried this SQL statement but no luck. ``` SELECT CONCAT(first_name, ' ', last_name) as fullname FROM users WHERE fullname LIKE '%Ludwig van Beethoven%'; ```

Original source