CODEWITHSUNDEEP
sql-server-2008t-sql
user680865
user680865

Reputation: 299

blank instead of zero

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

The Function above calculates number of days and if day is null it displays the value 0 instead of blank value

How can I change the above code to so that it shows blank and not 0 for value null?

Upvotes: 7

Views: 24737

Answers (5)

David Tinsley
David Tinsley

Reputation: 23

I use case statements and casting to do this.

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

Basically, you're changing your numeric to show either as a character or a blank. You will have to do all your math before you change to nvarchar though, because outside of this, it becomes a string. It would be helpful if BLANK was a command and worked with numeric values though.

Hope this helps someone.

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Try this:

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

Result:

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

You can have the space if you first cast 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 cast to varchar.

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

Summary:

You need this:

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

or this

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

Upvotes: 10

Johnv2020
Johnv2020

Reputation: 2146

You could declare Duration as a sql_variant datatype and allow implicit conversion to occur so something like this should work

declare @DURATION sql_variant

select COALESCE(@DURATION, '')

set @DURATION=1

select COALESCE(@DURATION, '')

Upvotes: 0

Jeremy Wiggins
Jeremy Wiggins

Reputation: 7299

Is FunctionA returning 0 instead of null? The code you've written may be ok, but if FunctionA never returns null then...

Upvotes: 0

user596075
user596075

Reputation:

If Duration is datatype int then you can't change that to an empty string (blank). You'll either have to change that to a string datatype (varchar for instance) or be okay with 0. int can either be NULL (if it is allowed) or a valid integer value. A blank string is not a valid integer value.

Upvotes: 2

Related Questions