I'm using Transact-SQL to provide a data source file for a text formatter/distributer which unfortunately doesn't have zero suppression. My output has 6 columns of numbers and each row only has values in 2 of those columns (it's an aged debtors report). I don't want the 0.00 to show in the other columns.
I tried this:
  isnull(cast(arage1 as char(15)),'') "arage1"
where column arage1 is originally decimal(16,2). But that still shows 0.00 in the output.

Any ideas please?

Doh! of course "isnull" won't work, the field isn't null - it's 0.00.
So, I want to do something like this:
   case when arage1=0 then'' else arage1 end
but of course it can't convert numeric to character. How do I tell it to do that? Thanks, Sharon.

What you're trying to do is poor practice, the data should be "pure", formatting etc. should be the purview of the client. If you want you can create a temporary table with NVARCHAR fields and insert your data into those using appropriate CASE expressions, CASE 0 THEN '' ELSE arage1.

--

Joe (Microsoft MVP - XML)

Thanks for the reply. Yes, I'm trying to get around a limitation in the client by being clever in my SQL output.
I've tried:
   case when arage1=0 then replace(cast(arage1 as nvarchar),'0','') else arage1 end,
but get error converting data type nvarchar to numeric.
I've also tried:
   case when arage1=0 then replace(cast(arage1 as nvarchar(1)),'0','') else arage1 end,
but get an arithmetic overflow.
However, when I do as you suggest and put the nvarchar results into a temp table, then case when those results, I do get my blanks.
Thanks so much, I was flailing with cast/convert/replace but this answer did solve my problem.

Sharon,

This should work... and without a CASE or a Temp Table...

ISNULL(NULLIF(columnname,0),' ')

--Jeff Moden

Thanks Jeff - I don't know about NULLIF yet. But I tried that syntax and got "Error converting data type varchar to numeric."
     select dw_nadr.mmatter, ISNULL(NULLIF(dw_nadr.arage1,0),' ') from dw_nadr
The column dw_nadr.arage1 is decimal(16,2) so I'm confused by the error message.

Shoot... my bad, Sharon... implicit conversion hierarchy got me... this will do the trick with the added benefit of lining up your numbers on the decimal point...

SELECT dw_nadr.mmatter, ISNULL(STR(NULLIF(dw_nadr.arage1,0),16,2),' ') AS ArAge1
FROM dw_nadr.

--Jeff Moden

You are a dream Jeff! That works great. I'll study the STR and NULLIF functions some more and I promise not to bother you with an easy one again.

Thanks Sharon,

By the way... if you don't know the answer to a question or which tools to look for, even seemingly simple problems are flat impossible. That's why folks started forums like this one. Never be afraid to ask even the "simple" questions if you've looked for but can't find the answer.

Thanks for taking the time to lookup the two functions... now, THAT is an excellent move.

Just as a kick start on those two... STR converts a number to a right justified number (left padded with spaces) and takes 3 operands...

STR(stringtoconvert,totalfinalwidth,numberofdecima lplaces)

I usually don't trust defaults but the 2nd operand defaults to 10 and the 3rd operand defaults to 0. Here's an "oolie" and I don't know if you'll ever need it and it's undocumented but if you ever need to do an odd thing called "Bankers Rounding", the STR function is the only thing in SQL Server that will do it.

The NULLIF function takes two operands...

NULLIF(thingtocheck,conditiontoreturnnull)

What it does is it checks the first operand against the second... if they match, the function returns NULL... if they don't match, the function returns the first operand. The following two code snippets are functionally equivelant...

NULLIF(SomeColumn,0)

CASE
  WHEN SomeColumn = 0
  THEN NULL
  ELSE SomeColumn
END

Write back if you have any additional questions...

"We're all in this together and I'm pullin' for ya" - Red Green

--Jeff Moden