如何将表中的所有空字符串更改为NULL? [英] How do I change all empty strings to NULL in a table?

问题描述

我有一个约有100列的旧表(90％可为空).在这90列中，我要删除所有空字符串并将其设置为null.我知道我可以做到:

I have a legacy table with about 100 columns (90% nullable). In those 90 columns I want to remove all empty strings and set them to null. I know I can:

update table set column = NULL where column = '';
update table set column2 = NULL where column2 = '';

但这很乏味且容易出错.必须在整个桌子上做到这一点吗?

But that is tedious and error prone. There has to be a way to do this on the whole table?

推荐答案

UPDATE
    TableName
SET
    column01 = CASE column01 WHEN '' THEN NULL ELSE column01 END,
    column02 = CASE column02 WHEN '' THEN NULL ELSE column02 END,
    column03 = CASE column03 WHEN '' THEN NULL ELSE column03 END,
    ...,
    column99 = CASE column99 WHEN '' THEN NULL ELSE column99 END

这仍然是手动执行的操作，但比您要痛苦的要少一些，因为它不需要您为每一列发送查询.除非您不愿意编写脚本，否则在执行此类操作时必须忍受一定的痛苦.

This is still doing it manually, but is slightly less painful than what you have because it doesn't require you to send a query for each and every column. Unless you want to go to the trouble of scripting it, you will have to put up with a certain amount of pain when doing something like this.

添加了END s

这篇关于如何将表中的所有空字符串更改为NULL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！