blocks|key|4984141|text|正如这里的许多人所说的，最好的方法是ISNULL()，但是，如果您想要一种简单的方法来生成所有这些ISNULL()，请使用以下代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4984142|SELECT+'ISNULL(['+%2B+COLUMN_NAME+%2B+'],+'+%2B+
++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+%2B+')+AS+['+%2B+COLUMN_NAME+%2B+'],'
FROM+INFORMATION_SCHEMA.COLUMNS+WHERE+TABLE_NAME+=+'TableName'|code-block|syntax|javascript|4984143|这将使单调乏味的工作变得简单得多，然后您只需编辑输出以说明各种字段类型(int、varchar、date等)|4984144|编辑:使用默认值记帐各种数据类型..|4984145|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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:

<pre><code>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'
</code></pre>

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)

Edit: accounting for various datatypes with default values..

blocks|key|775430|text|实际上，你有两个选择|type|unstyled|depth|inlineStyleRanges|entityRanges|data|775431|ISNULL(yourColumn,0)+AS+columnName|code-block|syntax|javascript|775432|或者实际修复问题并将所有空数据更改为0|775433|UPDATE+table
SET+column+=+0
WHERE
column+IS+NULL++++--+remember+"column+=+NULL"+always+equates+to+NULL!|775434|当你这样做的时候，不要忘了哪里或者所有的东西都会变成0！|775435|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You have two options really

<pre><code>ISNULL(yourColumn,0) AS columnName
</code></pre>

or to actually fix the issue and change all the NULL data to 0

<pre><code>UPDATE table
SET column = 0
WHERE
column IS NULL -- remember "column = NULL" always equates to NULL!
</code></pre>

When you do this dont forget the WHERE or everything will end up being 0!

blocks|key|4700811|text|您必须手动指定SELECT语句中的每一列，并使用ISNULL()函数：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4700812|SELECT+ISNULL(columnName,+0),+...+yes+all+of+them|code-block|syntax|javascript|4700813|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms184325.aspx^0|O|8|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

You have to manually specify each column in SELECT statement and use <a href="http://msdn.microsoft.com/en-us/library/ms184325.aspx" rel="nofollow">ISNULL()</a> function:

<pre><code>SELECT ISNULL(columnName, 0), ... yes all of them
</code></pre>

blocks|key|4983971|text|尽管你不愿意这么做，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4983972|ISNULL(FieldName,0)+AS+FieldName|code-block|syntax|javascript|4983973|for+each+column是处理此问题的正确方法。|4983974|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Despite your reluctance to do so,

<pre><code>ISNULL(FieldName,0) AS FieldName
</code></pre>

for each column is the correct way to handle this.

blocks|key|772110|text|做一个老板。写一些类似这样的东西：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|772111|select+'update+'+%2B+table_name+%2B+'+set+['+%2B+column_name+%2B+']+=+''''+where+['+%2B+column_name+%2B+']+is+null'
from+tempdb.information_schema.columns
where+table_name+=+'YourTableName'|code-block|syntax|javascript|772112|它会为你吐出一个很大的查询。|772113|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Be a boss. Write something like:

<pre><code>select 'update ' + table_name + ' set [' + column_name + '] = '''' where [' + column_name + '] is null'
from tempdb.information_schema.columns
where table_name = 'YourTableName'
</code></pre>

It'll spit out a big ol' query for you.

blocks|key|3085675|text|除了ISNULL，您还可以使用COALESCE，它与ANSI+SQL兼容。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3085676|select+coalesce(yourcolumn,+0)|code-block|syntax|javascript|3085677|3085678|有很多列，我不想像ISNULL(+FieldName+,0)那样遍历每一列作为FieldName。|blockquote|3085679|3085680|3085681|你不可能总能得到你想要的。太可惜了。这是一种方法:一列接一列。|3085682|entityMap^0|2|6|F|8|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|10|8|@]|D|@]|E|$]]|$1|L|3|M|5|N|7|11|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|12|8|@]|D|@]|E|$]]|$1|P|3|-4|5|6|7|13|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|14|8|@]|D|@]|E|$]]|$1|S|3|-4|5|6|7|15|8|@]|D|@]|E|$]]]|T|$]]

Alternatively to <code>ISNULL</code>, you can use <code>COALESCE</code>, which is ANSI SQL compatible.

<pre><code>select coalesce(yourcolumn, 0)
</code></pre>

<blockquote>
 There is lots of columns, I don't want to have to go through each one with something like ISNULL(FieldName,0) AS FieldName.
</blockquote>

You can't always get what you want. Too bad. This is the way to go: column by column.

blocks|key|4984194|text|要做到这一点并不容易，就像你所说的那样，不仔细阅读每一栏。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4984195|NULL对0有特殊而独特的含义。这意味着没有值或未知，而不是已知的零值。一些数据库纯粹主义者会争辩说，表中的一行根本不应该有NULL！|4984196|如果源表中的空值真的表示零，则更改它们，否则在查询中传播空值时对其进行处理。|4984197|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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!

If the NULLs in your source table really mean zero then change them, otherwise deal with the nulls as they propogate through your queries.

blocks|key|4701103|text|我不想用ISNULL(+FieldName+,0)这样的东西来遍历每一个FieldName。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4701104|4701105|接受攻击，对每个对象执行一次此操作：|4701106|ALTER+TABLE+TableName+ALTER+COLUMN+FieldName+INTEGER+DEFAULT+0+NOT+NULL;|code-block|syntax|javascript|4701107|然后，您可以忘记所有关于ISNULL、COALESCE、三值逻辑等的内容。|offset|length|style|CODE|4701108|entityMap^0|0|0|0|0|C|6|J|8|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|S|8|@]|9|@]|A|$]]|$1|C|3|D|5|6|7|T|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|U|8|@]|9|@]|A|$H|I]]|$1|J|3|K|5|6|7|V|8|@$L|W|M|X|N|O]|$L|Y|M|Z|N|O]]|9|@]|A|$]]|$1|P|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|Q|$]]

<blockquote>
 I don't want to have to go through each one with something like ISNULL(FieldName,0) AS FieldName.
</blockquote>

Take the hit and do this once for each one:

<pre><code>ALTER TABLE TableName ALTER COLUMN FieldName INTEGER DEFAULT 0 NOT NULL;
</code></pre>

You can then forget all about <code>ISNULL</code>, <code>COALESCE</code>, three-valued logic, etc.

blocks|key|4701153|text|我想要一个动态查询来执行更新选项。借用上面的帖子和another，我有以下几点：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4701154|++++++++++++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+'+%2B+@tableName+%2B+'+SET+'+%2B+quotename(@name)+%2B'+=+'+%2B+

+++++++++++++++(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+)+
++++++++++++%2B+'+WHERE+'+%2B+quotename(@name)+%2B'+IS+NULL'+

++++++++++++PRINT+@CMD+

++++++++++++EXEC+sp_executeSQL+@cmd+

++++++++++++FETCH+NEXT+FROM+db_cursor+INTO+@name,+@Dtype+

++++++++++++END+

++++++++++++CLOSE+db_cursor+
++++++++++++DEALLOCATE+db_cursor|code-block|syntax|javascript|4701155|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/6130133/sql-server-update-all-null-field-as-0/30512692#30512692^0|P|7|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

I wanted a dynamic query to do the update option. Borrowing from the post above and <a href="https://stackoverflow.com/questions/6130133/sql-server-update-all-null-field-as-0/30512692#30512692">another</a> I have the following:

<pre><code> 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
</code></pre>

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.

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.

There is lots of columns, I don't want to have to go through each one with something like ISNULL(FieldName,0) AS FieldName.

SQL replace all NULLs

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一张大桌子，里面有一些空的东西。从带有0而不是NULL的表中进行选择最简单的方法是什么？或者，如果没有简单的方法可以做到这一点，我愿意将表中的所有空值物理地替换为0。有很多列，我不想用ISNULL( FieldName ,0)这样的东西来遍历每一列作为FieldName。

问SQL替换所有空值
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL替换所有空值EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL替换所有空值
EN