SQL 替换所有 NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7930810/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL replace all NULLs
提问by Alec
I have a big table with some NULLs in it. What is the easiest possible way to select from the table with 0's instead of NULLs.
我有一个大表，里面有一些 NULL。从带有 0 而不是 NULL 的表中进行选择的最简单方法是什么。
Or if there is no easy way to do that, I'm willing to physically replace all the nulls with 0's in the table.
或者，如果没有简单的方法可以做到这一点，我愿意将表中的所有空值物理替换为 0。
There is lots of columns, I don't want to have to go through each one with something like ISNULL(FieldName,0) AS FieldName.
有很多列，我不想用 ISNULL(FieldName,0) AS FieldName 之类的东西来遍历每一列。
回答by Josh Weatherly
As many here have said, the best approach is ISNULL(), however if you want an easy way to generate all those ISNULL()'s use the following code:
正如这里的许多人所说，最好的方法是 ISNULL()，但是如果您想要一种简单的方法来生成所有这些 ISNULL()，请使用以下代码：
SELECT 'ISNULL([' + COLUMN_NAME + '], ' +
CASE
WHEN DATA_TYPE = 'bit' THEN '0'
WHEN DATA_TYPE = 'int' THEN '0'
WHEN DATA_TYPE = 'decimal' THEN '0'
WHEN DATA_TYPE = 'date' THEN '''1/1/1900'''
WHEN DATA_TYPE = 'datetime' THEN '''1/1/1900'''
WHEN DATA_TYPE = 'uniqueidentifier' THEN '00000000-0000-0000-0000-000000000000'
ELSE '''''' -- everything else get's an empty string
END + ') AS [' + COLUMN_NAME + '],'
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName'
This will make the tedious job a whole lot easier, you then just have to edit the output to account for the various field types (int, varchar, dates, etc)
这将使繁琐的工作变得更容易，然后您只需编辑输出以考虑各种字段类型（int、varchar、date 等）
Edit: accounting for various datatypes with default values..
编辑：使用默认值考虑各种数据类型..
回答by Purplegoldfish
You have two options really
你真的有两个选择
ISNULL(yourColumn,0) AS columnName
or to actually fix the issue and change all the NULL data to 0
或实际解决问题并将所有 NULL 数据更改为 0
UPDATE table
SET column = 0
WHERE
column IS NULL -- remember "column = NULL" always equates to NULL!
When you do this dont forget the WHERE or everything will end up being 0!
当你这样做时，不要忘记 WHERE 否则一切都会变成 0！
回答by sll
回答by Joe Stefanelli
Despite your reluctance to do so,
尽管你不愿意这样做，
ISNULL(FieldName,0) AS FieldName
for each column is the correct way to handle this.
对于每一列是处理这个问题的正确方法。
回答by Christophxr
Be a boss. Write something like:
做老板。写一些类似的东西：
select 'update ' + table_name + ' set [' + column_name + '] = '''' where [' + column_name + '] is null'
from tempdb.information_schema.columns
where table_name = 'YourTableName'
It'll spit out a big ol' query for you.
它会为您提供一个很大的查询。
回答by william-1066
I wanted a dynamic query to do the update option. Borrowing from the post above and anotherI have the following:
我想要一个动态查询来执行更新选项。从上面的帖子和另一个借用我有以下内容：
USE [YOUR DATABASE]
DECLARE @tableName nvarchar(100)
DECLARE @name varchar(50)
DECLARE @dtype varchar(50)
DECLARE @CMD NVARCHAR (200)
SET @tableName = [YOUR TABLE NAME]
DECLARE db_cursor CURSOR FOR
SELECT c.name, t.name AS Dtype
FROM sys.columns c
INNER JOIN sys.types t
ON t.system_type_id = c.system_type_id
WHERE c.[object_id] =
(SELECT [object_id] FROM sys.objects WHERE type = 'U' AND [NAME] = @tableName)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name, @Dtype
WHILE @@FETCH_STATUS = 0 BEGIN
SET @CMD = 'UPDATE ' + @tableName + ' SET ' + quotename(@name) +' = ' +
(CASE
WHEN (@Dtype = 'bit') THEN '0'
WHEN (@Dtype = 'int') THEN '0'
WHEN (@Dtype = 'decimal') THEN '0'
WHEN (@Dtype = 'date') THEN '''1/1/1900'''
WHEN (@Dtype = 'datetime') THEN '''1/1/1900'''
WHEN (@Dtype = 'uniqueidentifier') THEN '00000000-0000-0000-0000-000000000000'
ELSE ''''''
END )
+ ' WHERE ' + quotename(@name) +' IS NULL'
PRINT @CMD
EXEC sp_executeSQL @cmd
FETCH NEXT FROM db_cursor INTO @name, @Dtype
END
CLOSE db_cursor
DEALLOCATE db_cursor
回答by Stephen Turner
It's not easy to do it without going through each column just as you said.
像你说的那样，不经过每一列，做起来并不容易。
NULL has a special and distinct meaning to 0. It means there is no value or unknown as opposed to a known zero value. Some database purists would argue that a row in a table shouldn't have NULLs in at all!
NULL 对 0 有特殊且不同的含义。它意味着没有值或未知，而不是已知的零值。一些数据库纯粹主义者会争辩说，表中的一行根本不应该包含 NULL！
If the NULLs in your source table really mean zero then change them, otherwise deal with the nulls as they propogate through your queries.
如果源表中的 NULL 确实意味着零，则更改它们，否则在它们通过查询传播时处理空值。
回答by onedaywhen
I don't want to have to go through each one with something like ISNULL(FieldName,0) AS FieldName.
我不想用 ISNULL(FieldName,0) AS FieldName 之类的东西来检查每一个。
Take the hit and do this oncefor each one:
抓住机会，为每个人做一次：
ALTER TABLE TableName ALTER COLUMN FieldName INTEGER DEFAULT 0 NOT NULL;
You can then forget all about ISNULL, COALESCE, three-valued logic, etc.
然后你可以忘记所有关于ISNULL, COALESCE, 三值逻辑等。
回答by Adriano Carneiro
Alternatively to ISNULL, you can use COALESCE, which is ANSI SQL compatible.
或者ISNULL，您可以使用COALESCE与 ANSI SQL 兼容的 。
select coalesce(yourcolumn, 0)
There is lots of columns, I don't want to have to go through each one with something like ISNULL(FieldName,0) AS FieldName.
有很多列，我不想用 ISNULL(FieldName,0) AS FieldName 之类的东西来遍历每一列。
You can't always get what you want. Too bad. This is the way to go: column by column.
你不能总是得到你想要的。太糟糕了。这是要走的路：逐列。
