Search code examples
mysqlsqlstringwhere-clausesql-like

mysql query LIKE condition against a concat

Im trying to run a query something like the following statement but either I can't figure out the correct syntax or it's not doable:

SELECT 
    id, first_name, last_name, 
    concat(first_name,' ',last_name) AS full_name
FROM mytable 
WHERE full_name LIKE '%scott%'

which is it? If its doable can I get a little help on the syntax?

Solution

  • You cannot use a field computed in the SELECT clause in the WHERE clause, because most (if not all) RDBMS evaluate the WHERE clause before the SELECT.

    You probably want :

    SELECT 
    id, first_name, last_name, 
    CONCAT(first_name,' ',last_name) AS full_name
FROM mytable 
WHERE CONCAT(first_name,' ',last_name) LIKE '%scott%'

    Which can also be expressed : 

    SELECT 
    id, first_name, last_name, 
    CONCAT(first_name,' ',last_name) AS full_name
FROM mytable 
WHERE first_name LIKE '%scott%' OR last_name like '%scott%'