在大量列中用 NULL 值替换空单元格 [英] Replace empty cells with NULL values in large number of columns

问题描述

我有包含大量列的 SQL 表.出于某种原因，某些列具有空单元格而不是 NULL 单元格.我想让所有列中的所有空单元格都为 NULL.

I have SQL table that has a large number of columns. For some reason, some columns have empty cells instead of NULL cells. I would like to make all empty cells in all the columns to be NULL.

我知道单列的方法是:

 UPDATE your_table SET column = NULL WHERE column = ''

但是，我不确定如何对所有列有效地执行类似的逻辑，而不必一一写入列名.

However, I am not sure how to execute a similar logic efficiently for all columns without having to write the column names one by one.

谢谢，

推荐答案

运行以下查询:

SELECT 'UPDATE yourtable SET ' + name + ' = NULL WHERE ' + name + ' = '''';'
FROM syscolumns
WHERE id = object_id('yourtable')
  AND isnullable = 1;

这个查询的输出将是一个像这样的 SQL 脚本块:

The output of this query will be a chunk of SQL script like this:

UPDATE yourtable SET column1 = NULL WHERE column1 = '';
UPDATE yourtable SET column2 = NULL WHERE column2 = '';
UPDATE yourtable SET column3 = NULL WHERE column3 = '';
-- etc...

将该 SQL 脚本复制并粘贴到新查询中并运行它以更新所有列.

Copy and paste that SQL script into a new query and run it to update all your columns.

这篇关于在大量列中用 NULL 值替换空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！