user3502587 user3502587 - 1 year ago 81
SQL Question

replace 0 with blank spaces

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

Answer Source

If exp is a numeric type you'll need to convert it to a string using CAST or 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 varchar and nvarchar in CAST and 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:

nullif(exp1, 0) exp1

This will return NULL if 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download