Output NULL if numeric field equals 0

Question

question

mbatchelor55 asked Mar 8, '18

Output NULL if numeric field equals 0

I am trying to use "CASE" to check a numeric field for 0 and output NULL. I have tried everything I know. I must have mental block. Please somebody HELP!

sql query

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-03-08T14:45:33Z

Oleg answered Mar 8, '18

You can use the [NULLIF][1]. For example: select nullif(SomeNumericField, 0) SomeNumericField from SomeTable; go The script above will return NULL for those values which are equal to 0. Of course the values which are NULL will also show up as NULL. Some people don't like using NULLIF because it is syntactic sugar and restate it as CASE statement, i.e. select case when SomeNumericField = 0 then null else SomeNumericField end SomeNumericField from SomeTable; Both versions are actually the same as far as the engine is concerned. In fact, even if you use NULLIF, the statement is restated by the parser as CASE statement, so there is no harm (or benefit) of using NULLIF vs CASE and vice versa. Hope this helps. Oleg [1]: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/nullif-transact-sql

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mbatchelor55 commented · Mar 08, 2018 at 02:50 PM

Oleg, this worked. I tried this with the "CASE" several times and it came back with this message: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.

0 ·

mbatchelor55 mbatchelor55 commented · Mar 08, 2018 at 02:51 PM

Thank you so very much.

0 ·

Oleg commented · Mar 08, 2018 at 03:09 PM

@mbatchelor55 This error message means that the data type of the column in question is not numeric but sadly, is a character based type, such as varchar. If this is the case then to make your CASE statement work all you have to do is replace the part reading **when SomeNumericField = 0 then null** to read **when SomeNumericField = '0' then null**. Actually, you should do it with NULLIF as well because the same error will occur whenever there are values which are not numeric. It is always evil to torture the engine by forcing it to compare different types which it can implicitly convert, so why do it? If the data type of the column in question is not numeric then it is easy enough to wrap that zero in single quotes to avoid any possible problems: ***nullif(SomeAlphaNumericField, '0')***. Even if all values happen to be numeric (just designed as varchar) and also include blank values, the blanks will also return NULL by both CASE and NULLIF. This is because the engine will implicitly convert the column values to integer which means that the empty space will be converted to 0, which might not be what you want.

0 ·

question