MS SQL server: display blank for a zero-valued or NULL-valued integer field

Question

I am able to use this:

select COALESCE(my_integer_field, '') from my_table 

to display zeros for NULL values. How can I not to display anything (no zeros) for NULL or zero values, without resorting to stored procedures?

Answer 1

It looks like a job for presentation layer not DB.

Anyway you could use:

SELECT CASE WHEN my_integer_field = 0 OR my_integer_field IS NULL THEN ''
       ELSE  CAST(my_integer_field AS VARCHAR(10))
       END
FROM my_table;

or using IIF (SQL Server 2012+):

SELECT IIF(my_integer_field = 0 OR my_integer_field IS NULL, 
           '',
           CAST(my_integer_field AS VARCHAR(10)))
FROM my_table;

LiveDemo

Keep in mind that blank (empty string '') will change the datatype of INT to string.

---

Or even shorter:

SELECT *,COALESCE(NULLIF(CAST(my_integer_field AS VARCHAR(10)) ,'0'), '') AS result
FROM my_table;

LiveDemo2

Output:

╔══════════════════╦════════╗
║ my_integer_field ║ result ║
╠══════════════════╬════════╣
║ NULL             ║        ║
║ 0                ║        ║
║ 1                ║      1 ║
║ 2                ║      2 ║
╚══════════════════╩════════╝