Empty instead of zero

Question

Duration = isnull(FunctionA(DateA,DateB),'') 

The function above calculates the number of days, and if the day is zero, it displays a value of 0 instead of an empty value

How can I change the above code so that it displays an empty value, not 0 for a null value?

Answer 1

If your function returns an integer, the result from isnull will also be an integer. In case the return value is null , you will have an implicit conversion to an integer for '' and this will be 0.

Try the following:

 declare @xx int select isnull(@xx,'') 

Result:

 ----------- 0 

You may have a space if you first passed the return value from your function to varchar .

 declare @xx int select isnull(cast(@xx as varchar(10)),'') 

Result:

 ---------- . 

If your function returns 0 instead of null , you can use nullif to get a null value before you click on varchar .

 declare @xx int = 0 select isnull(cast(nullif(@xx, 0) as varchar(10)),'') 

Summary :

You need the following:

 Duration = isnull(cast(FunctionA(DateA,DateB) as varchar(10)),'') 

or

 Duration = isnull(cast(nullif(FunctionA(DateA,DateB), 0) as varchar(10)),'') 

Answer 2

If Duration is an int data type, you cannot change it to an empty string (empty). You will have to either change this to a string data type (e.g. varchar ) or be ok with 0 . int can be NULL (if allowed) or a real integer value. An empty string is not a valid integer value.

Answer 3

I use case and casting arguments for this.

Example: case when columnX <> 0, then throws (columnX as nvarchar) else '' end

Basically, you change your number to show either a character or a space. You will have to do all of your math before switching to nvarchar, because outside this it becomes a string. It would be useful if BLANK was a team and worked with numeric values.

Hope this helps someone.

Answer 4

Is FunctionA returning 0 instead of zero? The code you wrote may be fine, but if FunctionA never returns null , then ...

Answer 5

You can declare Duration as sql_variant data type and enable implicit conversion so something like this should work

declare @DURATION sql_variant

select COALESCE (@DURATION, '')

set @DURATION = 1

select COALESCE (@DURATION, '')