user3502587 user3502587 - 28 days ago 13
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

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.