How to write a WHERE statement based on the value of a CASE statement?

mysql

Solution

If you repeat the `case` in the `where` clause, the statement will work:

SELECT
CASE
    WHEN nickname = '' THEN fullname ELSE nickname END AS new_name      
FROM names
WHERE (CASE WHEN nickname = '' THEN fullname ELSE nickname END) LIKE '%Joh%'

It wouldn't be fast, because it wouldn't use indexes, but it should work.

A somewhat better approach would be as follows:

SELECT
CASE
    WHEN nickname = '' THEN fullname ELSE nickname END AS new_name      
FROM names
WHERE (nickname like '%Joh%') or (fullname LIKE '%Joh%')

This will return the same results, but it can use indexes on `nickname` and `fullname` if you define them, EDIT and change the second operand of `LIKE` to not use the leading `%`.

Problem

The following query returns a result set of all the `new_name` from the table `names` ``` SELECT CASE WHEN nickname = '' THEN fullname ELSE nickname END AS new_name, FROM names ``` I'm trying to search by `new_name` using the following query ``` SELECT CASE WHEN nickname = '' THEN fullname ELSE nickname END AS new_name, FROM names WHERE new_name LIKE '%Joh%' ``` However I get the error Unknown column 'new_name' in 'where clause' Is there a way I get this functionality to work without using a subquery?

Original source