Convert 0 or zeros to empty string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Convert 0 or zeros to empty string

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2013-11-27 : 14:24:00

Usually this is not so frustrating but I'm going around in circles again.

There are about 20 fields, all floats.
Some values are 0 and others are 0.0000000000

If the value is any of these or combination, it should return an empty string.

I've been trying case statements, ISNULL, IFNULL and keep getting conversion errors.

Also, if it's not 0 or 0.00..., then it needs to have at least 5 decimal places - trailing zeros included.

Any input is appreciated.

Thanks!

EDIT:

I have come up with this that seems to work, even when NULL...


CASE CONVERT( FLOAT, myValue) WHEN 0 THEN '' 
ELSE CONVERT(DECIMAL(10,5), myValue)

But since there are about 20 fields to adjust, Is there an easier way without creating a function?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-27 : 15:02:03

Are you dealing with scientific data? Float should almost never be used. Can't you convert it to decimal and then do the formatting in the application?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-27 : 15:05:56

If this output is for a report of some kind then you should handle that in the reporting application. The problem is you are trying to mix data types. Do you want the floats to be returned as varchar? I would hope not but if so then maybe:

case when myValue = 0 then '' else convert(varchar(30), myValue) end

this will keep NULLs as NULL, the floats will come out "looking" like they do from a select statement and flavors of zero will come out as empty string. But the output must all be the same data type.

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2013-11-29 : 03:48:10

quote:

Originally posted by Zath


CASE CONVERT( FLOAT, myValue) WHEN 0 THEN '' 
ELSE CONVERT(DECIMAL(10,5), myValue)

Can't that just be:


NullIf(CONVERT(DECIMAL(10,5), myValue), 0.0)

??

It will return NULL rather than Blank String, but hopefully the front end can deal with displaying "nothing" to the user?

If you need the result as a VARCHAR (bad idea, front end won't treat it as a number, so you may get issues like not aligning-right and not sorting in numeric order and so on) this would do:


COALESCE(CONVERT(varchar(20), NullIf(CONVERT(DECIMAL(10,5), myValue), 0.0), '')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources