integer to blank

How do I set an integer to blank ''  when it's displayed as a 0?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Darrell PorterEnterprise Business Process ArchitectCommented:
In the results of a query?
In the output of a report?
When viewing tables interactively?
Where do you want to view zero values as null or empty?
eluceroAuthor Commented:
its in a sql query.
eluceroAuthor Commented:
results of a query
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

eluceroAuthor Commented:
A integer datatype cannot be '' correct??
Mario RichardsonHealthcare AnalystCommented:
Hello Elucero!

So I am going to say if you are trying to pull back a dataset that has a column [int] and where there exists 0 (Zero) you can do this.

You can create a case statement like this:

CASE WHEN COL1 = 0 THEN '' ELSE CONVERT(varchar(20), COL1)  END  as 'Alias Name of Column'

Open in new window

PortletPaulEE Topic AdvisorCommented:
does it really have to be '' ?? would NULL be sufficient ? e.g.

select case when column1 = 0 then NULL else column1 end as column1
from yourtable

otherwise you do have to convert the column to a string because '' is a string.
Olaf DoschkeSoftware DeveloperCommented:
Anything has been said aside of a solution many frontend languages offer to display numbers differently. Eg accounting often wants blanks instead of 0, but furthermore also negative numbers in brackets instead of minus sign and ideally red. And such things are the topic of formatting and not of SQL, even though SQL has a "recent" new FORMAT() function, that only is about putting together a number with formatting characters like thousand separators or any other formatting issues and also that puts it from int or numeric to string type.

Keep your SQL result as int and look for ways to format that frontend wise. It's easy enough if the frontend is HTML you have means to control this with if conditions and even color it the way you like.

Bye, Olaf.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
An empty string '' is not compatible with an int data type, so if you convert it to a varchar to get the ''s but you lose the ability to do math expressions like SUM and COUNT.

*Really* recommend passing a NULL in the SQL query and keeping the column an int, and then let the front-end reporting layer (VB, Java, SSRS, whatever) cosmetically render the NULL to an empty string '', and along for that ride other cosmetics such as thousands separators, brackets for negative numbers, whatever.

+1 Olaf's comment.
Mark WillsTopic AdvisorCommented:
>> A integer datatype cannot be '' correct??

Correct. You have to convert (or cast) to a character based column.

While Olaf makes an intersting point about the presentation layer doing the hard work, we do sometimes need to help the presentation layer. I am not sure about how to use FORMAT to transform a numeric to blank - pretty sure it cant be done. Can most definitely be used to convert to a string. But there are other ways (including NULL)
declare @int int = 0

select NULLIF(@int,0),           -- sql 2008
       CASE when @int = 0 then '' else cast(@int as varchar) end,    -- sql early days
       IIF(@int = 0, '', cast(@int as varchar))            -- SQL 2012

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
I just mentioned FORMAT for its general purpose, not hinting at it offering a solution. The final truth is the number is 0 and the wish is to change its display. There are viable reasons for this, but they don't need to be resolved at the level of the query already.

Bye, Olaf.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.