Display 'blank' cell when it is null.

Question

Display 'blank' cell when it is null.

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Kenean Say Hey Kid Points: 663 More actions · Answer 1

Thank you all! FYI ~ someone also suggested this along with the description:
COALESCE(STR(columnname),'')
'Numeric types can NEVER be blank. They either have a value, or they're NULL. "Blank" really means "empty string", and you just can't put string values into number type data. If you want to see blanks, you have to do this:

COALESCE(STR(columnname),'')

Just remember: now this is a varchar column in the results, and not a decimal(9,2) any more. Anything downstream that wants to use those numbers will have to re-parse the string data into numbers again.

The best option is usually to allow the database server to return NULL, and have the presentation end of the system worry about what to do with those values... but I know that's not always possible.'

Eric M Russell SSC Guru Points: 125562 More actions · Answer 2

What's a "cell" ?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Kenean Say Hey Kid Points: 663 More actions · Answer 3

I meant a single record (a single row record of a specific column)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

keneangbu - Monday, October 22, 2018 8:38 AM
Thank you all! FYI ~ someone also suggested this along with the description:
COALESCE(STR(columnname),'')
'Numeric types can NEVER be blank. They either have a value, or they're NULL. "Blank" really means "empty string", and you just can't put string values into number type data. If you want to see blanks, you have to do this:
COALESCE(STR(columnname),'')
Just remember: now this is a varchar column in the results, and not a decimal(9,2) any more. Anything downstream that wants to use those numbers will have to re-parse the string data into numbers again.
The best option is usually to allow the database server to return NULL, and have the presentation end of the system worry about what to do with those values... but I know that's not always possible.'

Lordy, no. STR() is slow. It's not as bad as the relatively new FORMAT function but it's a lot slower than Cast or Convert. Please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/71565/

Now... can you answer my question about what you're doing? If the original column is Decimal(9,2), why are you converting to FLOAT to start with??? :blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kenean Say Hey Kid Points: 663 More actions · Answer 5

Jeff, thanks for the interesting takeaway about STR(). For your question about why I need to convert the decimal(9,2) column to FLOAT, it is because of the data conversion error. Would you suggest anything else, please?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

keneangbu - Tuesday, October 23, 2018 7:30 AM
Jeff, thanks for the interesting takeaway about STR(). For your question about why I need to convert the decimal(9,2) column to FLOAT, it is because of the data conversion error. Would you suggest anything else, please?

You have to decide on what you truly need for the output column to be for datatype. One thing that keneangbu was absolutely correct about is that you cannot have character based data in a DECIMAL(9,2) column and that empty or blank strings will be interpreted as a "0".

drew.allen was also the first on this thread to state that problem and he also stated that it's better to let the "Presentation Layer" handle such formatting and I strongly agree with that. Unfortunately, the "Presentation Layer" is sometimes the output of a SELECT statement, especially if you're writing to a file or someone decided that the presentation layer needs to be originated from the SELECT (I create HTML for my DBA "Morning Reports" and returning empty "cells" instead of a "0" where NULLs are present is frequently necessary).

With the understanding that the only way you can substitute blanks for NULLs is to output the result column as a character based column and that should only be done for "presentation" purposes, the following code will do just fine without skipping through FLOAT. If you need to have the data "decimal aligned", that's a bit of a different story but can be still be done to be faster than using STR().

SELECT COALESCE(CONVERT(VARCHAR(20),ColumnName),'') FROM dbo.SomeTableName ;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)