integer to blank

How do I set an integer to blank ''  when it's displayed as a 0?
Who is Participating?
Mark WillsConnect With a Mentor Topic 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

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.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

eluceroAuthor Commented:
results of a query
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

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 DoschkeConnect With a Mentor Software 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 HornConnect With a Mentor Microsoft 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.
Olaf DoschkeConnect With a Mentor Software 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.