[sql] replace NULL with Blank value or Zero in sql server

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.

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

Replace Null Values as Empty: ISNULL('Value','')

Replace Null Values as 0: ISNULL('Value',0)

Different ways to replace NULL in sql server

Replacing NULL value using:

1. ISNULL() function

2. COALESCE() function

3. CASE Statement

SELECT Name as EmployeeName, ISNULL(Bonus,0) as EmployeeBonus from tblEmployee

SELECT Name as EmployeeName, COALESCE(Bonus, 0) as EmployeeBonus 
FROM tblEmployee

SELECT Name as EmployeeName, CASE WHEN Bonus IS NULL THEN 0 
ELSE Bonus  END as EmployeeBonus 
FROM  tblEmployee

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  

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