I want to show blank '' instead of Zero value of Expression field. If exp is of INT datatype. whenever I try to use(case when exp1 is 0 then '' else exp1 end as exp1)but it still gets 0 as output.any help appreciated. Thanks
exp is a numeric type you'll need to convert it to a string using
CONVERT. Also, I don't believe
exp1 is 0 will work; I think you're looking for
exp1 = 0 instead.
Try something like this this:
(case when exp1 = 0 then '' else cast(exp1 as varchar(30)) end) as exp1 (case when exp1 = 0 then '' else convert(varchar(30), exp1) end) as exp1
Or using a simple
CASE expression, like this:
(case exp1 when 0 then '' else cast(exp1 as varchar(30)) end) as exp1 (case exp1 when 0 then '' else convert(varchar(30), exp1) end) as exp1
Note: The default length for
CONVERT is 30, so
cast(exp1 as varchar) or
convert(varchar, exp1) would work as well, but as a matter of practice it's best to specify the lengths of these types whenever you use them.
However, if what you'd rather do is convert the value 0 to
null, it's fairly easy. Just use
nullif(exp1, 0) exp1
This will return
exp1 evaluates to 0, otherwise it will return the value of
exp1. When you're inserting this value into a table, make sure the column you are inserting it into is nullable. If you're not familiar with using null, see the Wikipedia article on the topic for more information.