在空白值中替换NULL或在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.

我有一个临时表，我的列total_amount之一是整数类型而非NULL。在查询数据时，我收到了total_Amount列的NULL值。

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

我怎么用下面的语法来删除空值但是仍然有一些NULL值出现，如果我错了就纠正我。

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.

我在我的其他部分面临问题。

6 个解决方案

#1

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

您可以使用COALESCE函数自动将空值返回为0.语法如下所示：

SELECT COALESCE(total_amount, 0) from #Temp1

#2

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.

当存在多个列[和] / [或]值并且您想要第一个时，coalesce（）是最佳解决方案。但是，在线查看书籍时，查询优化会将其转换为案例陈述。

MSDN excerpt

MSDN摘录

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

COALESCE表达式是CASE表达式的语法快捷方式。

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

也就是说，代码COALESCE（expression1，... n）由查询优化器重写为以下CASE表达式：

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?

有了这个说，为什么不是一个简单的ISNULL（）？少代码=更好的解决方案？

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?

最后但同样重要的是，如何将空值转换为NOT NULL列？

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.

我不得不更改表定义，以便我可以设置测试用例。当我尝试将表更改为NOT NULL时，它会失败，因为它执行了可空性检查。

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

#3

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.

在第一个你有一个角色，在其他你有一个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

#4

Different ways to replace NULL in sql server

在sql server中替换NULL的不同方法

Replacing NULL value using:

使用以下代替NULL值：

1. ISNULL() function

1. ISNULL（）函数

2. COALESCE() function

2. COALESCE（）功能

3. CASE Statement

3. CASE声明

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

#5

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

将空值替换为空：ISNULL（'值'，''）

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

将空值替换为0：ISNULL（'Value'，0）

#6

Try This

尝试这个

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

要么

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

#1