mysqlsqlnullif

Nested NULLIF (Assign value of a column to null if (condition A) or (condition B)) in SQL without CASE

I have a column C1 with values that can either be 'values' or 'empty' or 'N/A'.

|  C1 |
-------
|     |
| N/A |
|apple|

I want to SELECT column C1 in a way that it converts empty values and N/A to NULL using NULLIF.

|  C1 |
-------
|NULL |
|NULL |
|apple|

We can do NULLIF(C1, '') which gives NULL if the column value is empty.

We can also use CASE and implement both case that way but I want to know if there is a way to use NULLIF for it and if so, how? (or any way other than CASE)

Something like NULLIF(C1, '' OR 'N/A')

Thanks in advance.

Solution

  • You can do it with a nested NULLIF():

    NULLIF(NULLIF(c1, ''), 'N/A')

    See the demo.