1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
Arsalan
  • 65
  • 1
  • 8

2 Answers2

3

You can do it with a nested NULLIF():

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

See the demo.

forpas
  • 160,666
  • 10
  • 38
  • 76
1

Use a case expression:

(case when c1 not in ('', 'N/A') then c1 end)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yeah, I know we can use CASE(as specified in the the question), but can we use any way other than CASE? Thanks for the reply tho :) – Arsalan Aug 04 '21 at 15:56
  • 2
    `NULLIF` is nothing more than syntactic sugar for a case expression anyway so there is little or no functional difference between NULLIF and CASE, and in those cases it is always best to opt for method that is easiest to read (not just for you but for anyone else that may read your query). I personally think this case expression is more readable than nested `NULLIF`s, but that is just personal preference – GarethD Aug 04 '21 at 16:05