-1

Is there a way to replace "null" values in a result table with a string like "Empty"? Similarly, can we generalize this to replacing a given string or output type with a replacement?

E.g. Original result:

   Dog  Cat Other  
1  1    0   5  
2  5    6   null  
3  45   8   4

New result after replacement:

   Dog  Cat Other  
1  1    0   5  
2  5    6   N/A  
3  45   8   4

Where N/A is the new string.

Clarification:
I am not looking to update the actual data in the table. I only want the printed query result to replace the null values.

Improve this question
8
  • 1
    Use Coalesce([Other], 'N/A') If it is something other than a string datatype, you can also do Coalesce(Str([Other]), 'N/A') Commented Nov 10, 2014 at 20:54
  • Yeah there is a way, if you were to alter the columns and make them not null Commented Nov 10, 2014 at 20:54
  • Do you want to update the table? Or just use it in a SELECT query? Commented Nov 10, 2014 at 20:54
  • 1
    Only if you cast the presumably numeric Other column to string. Should be done in your application. Commented Nov 10, 2014 at 20:54
  • 1
    OK someone clearly went on a downvote spree for no reason.. Commented Nov 10, 2014 at 20:59

3 Answers 3

Reset to default
1 
COALESCE (str(<columnName>),'Empty')

this will use column value if not not null and 2nd argument if it is null.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You could use ISNULL function:

ISNULL([Other], 'N/A')

An application:

SELECT [Dog], [Cat], ISNULL([Other], 'N/A')
WHERE ...
Improve this answer

7 Comments

Can anybody explain the down voting in every post/comment of this question?
Not the downvoter but this answer and the others assumes that Other is already a string not numeric.
That's fair, but I'm pretty sure that ISNULL should work in t-sql. Can anyone try it? I'm not able to try the example right now.
If the datatypes are not the same, you're going to get an error. You can't have a string in a numeric column.
Even if he wants to display data, without saving it? So, the question has no solution, am I wrong?
|
-1

Replace the SELECT Other with 

CASE WHEN Other IS NULL THEN 'N/A' ELSE Other END
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.