Return blank instead of 0 in sql case when statement-CodePudding

In the SQL view design, I want to add column with CASE WHEN statement:

SELECT  
      [A]
      ,[B]
      ,[C]
      ,[VALUE]
      ,[D]
      ,[E]
      ,CASE WHEN [VALUE] > 0 THEN [VALUE] ELSE '' END AS VALUE1
      ,[DATE]
  
  FROM [MY_DATABASE].[dbo].[MY_VIEW]

However, this returns 0 instead of blank. How can I get blank for ''?

I have tried Cast() to convert 0 into varchar(10) as suggested in this answer but couldn't seem to get it work. Any help is appreciated.

CodePudding user response：

It returns zero because the first THEN expression of the CASE is a number (VALUE), and the compiler converts the second expression (ELSE '') to a number also, to make the results of all CASE branches compatible.

You could try returning string for both (THEN and ELSE parts), instead:

, CASE WHEN [VALUE]>0 THEN CAST(VALUE as varchar(50)) ELSE '' END as VALUE1

However, I can't tell if this is the right approach for you because the VALUE may have fractional/exponential parts, and simple cast to varchar(50) may not show them the way you want to show. If that is the case then you can try the FORMAT function (if you are using SQL Server), instead of CAST (FORMAT should also return VARCHAR)