Display NULL instead of zero

Question

I want to get NULL values instead of zero in my query.I put a condition where the varchar '0.0000' is not supposed to display along with NULL value. When the outcome comes, NULL is not displayed but 0.0000 is still showing there. Is there anyway i can convert that varchar to NULL so that the value is not displayed?

Please show what query have you written – Hitesh Jun 13 '14 at 12:38 — Hitesh, Jun 13 '14 at 12:38
Is the column in your table nullable? – Jens Jun 13 '14 at 12:39 — Jens, Jun 13 '14 at 12:39

score 3 · Answer 1 · answered Jun 13 '14 at 12:38

3

You can use NULLIF:

Returns a null value if the two specified expressions are equal.

SELECT NULLIF(Column, '0.0000') As Column
FROM dbo.TableName

answered Jun 13 '14 at 12:38

Tim Schmelter

450,073
74
686
939

@Patrick: "Is there anyway i can convert that varchar to NULL?" So he wants to treat the varchar `'0.0000'` as `NULL`. – Tim Schmelter Jun 13 '14 at 12:41
1

@PatrickHofman: yes, exactly what `NULLIF` does. – Tim Schmelter Jun 13 '14 at 12:42
what about IF() `IF(Expressio_TRUE, VAL1, NULL)` – Blood-HaZaRd Jun 13 '14 at 12:43
1

@PatrickHofman - are you perhaps thinking of `ISNULL`/`COALESCE`? That's not what Tim's recommending. – Damien_The_Unbeliever Jun 13 '14 at 12:43
@Damien_The_Unbeliever: You are right, my bad. Tim: my apologies. – Patrick Hofman Jun 13 '14 at 12:44

score 1 · Accepted Answer · answered Jun 13 '14 at 12:38

1

select case when your_column = '0.0000' then null
            else your_column
       end as your_column
from your_table

answered Jun 13 '14 at 12:38

juergen d

201,996
37
293
362

Thank you all for your answer. For me, the one with Select CASE worked. – user3737628 Jun 13 '14 at 13:10

Display NULL instead of zero

2 Answers2