mysql query LIKE condition against a concat

Question

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?

Answer 1

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%'

Answer 2

There are a few ways that I can think of you can do this:

1. You could run the LIKE against both the first_name and last_name columns separately using an OR, or
2. You could use a sub-query to concatenate the fields first, or
3. You could run the LIKE against the concatenated fields.

For the first solution:

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%');

For the second solution:

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

The third involves including WHERE CONCAT(...) LIKE '%scott%'.

I would personally use the second solution as I believe it would be more efficient, and I think it looks more presentable. They'll all work though so it's up to you.