replace NULL with Blank value or Zero in sql server

Question

I have a temp table and in that one of my column total_amount is of integer type and NOT NULL. While querying data, I received NULL values for total_Amount column.

How ever I used following syntax to remove nulls but some how still NULL values appearing, correct me if I am wrong.

Create table #Temp1
(
    issue varchar(100) NOT NULL,
    total_amount int NOT NULL
) 

This is my query

Case when total_amount = 0 then 0  
else isnull(total_amount, 0)  
end as total_amount  

I am facing issue at my else part.

Answer 1

You can use the COALESCE function to automatically return null values as 0. Syntax is as shown below:

SELECT COALESCE(total_amount, 0) from #Temp1

Answer 2

Try This

SELECT Title  from #Movies
    SELECT CASE WHEN Title = '' THEN 'No Title' ELSE Title END AS Titile from #Movies

OR

SELECT [Id], [CategoryId], ISNULL(nullif(Title,''),'No data') as Title, [Director], [DateReleased] FROM #Movies

Answer 3

The coalesce() is the best solution when there are multiple columns [and]/[or] values and you want the first one. However, looking at books on-line, the query optimize converts it to a case statement.

MSDN excerpt

The COALESCE expression is a syntactic shortcut for the CASE expression.

That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression:

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   WHEN (expression2 IS NOT NULL) THEN expression2
   ...
   ELSE expressionN
END

With that said, why not a simple ISNULL()? Less code = better solution?

Here is a complete code snippet.

-- drop the test table
drop table #temp1
go

-- create test table
create table #temp1
(
    issue varchar(100) NOT NULL,
    total_amount int NULL
); 
go

-- create test data
insert into #temp1 values
    ('No nulls here', 12),
    ('I am a null', NULL);
go

-- isnull works fine
select
    isnull(total_amount, 0) as total_amount  
from #temp1

Last but not least, how are you getting null values into a NOT NULL column?

I had to change the table definition so that I could setup the test case. When I try to alter the table to NOT NULL, it fails since it does a nullability check.

-- this alter fails
alter table #temp1 alter column total_amount int NOT NULL

Answer 4

You should always return the same type on all case condition:

In the first one you have an character and on the else you have an int.

You can use:

Select convert(varchar(11),isnull(totalamount,0))

or if you want with your solution:

Case when total_amount = 0 then '0'   
else convert(varchar(11),isnull(total_amount, 0))  
end as total_amount