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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Replace Null Values as Empty  ISNULL  Value       Replace Null Values as 0  ISNULL  Value  0

User · Answer

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

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

Examples related to sql

Examples related to sql-server-2008