有没有办法在SQL Server中替换整个表的刺痛

Question

我正在尝试对整个表使用替换，因为数据包含"NULLS"，因为它随着时间的推移已纵以删除某些不正确的值等，现在包含大量 NULL，我想将所有"NULL"值替换为表中所有列的 NULL。

我考虑过使用：

Replace(Column, 'NULL', NULL) 

我有第 2 - 17 列(因为 1 是 id(，但我想知道是否有人知道如何在替换语句中指定所有表或用 NULL 替换所有"NULL"的另一种方法。

Answer 1

您可以创建语句

select 'update student set ' + column_name + ' = null where ' + column_name + ' = ''null'';'
from information_schema.columns
where table_name = 'student'
order by ordinal_position

Answer 2

您需要分别为每列执行此操作。

UPDATE YourTable
SET Column1 = CASE Column1 WHEN 'NULL' THEN NULL ELSE Column1 END,
SET Column2 = CASE Column2 WHEN 'NULL' THEN NULL ELSE Column2 END,
SET Column3 = CASE Column3 WHEN 'NULL' THEN NULL ELSE Column3 END,
...
SET Column50 = CASE Column50 WHEN 'NULL' THEN NULL ELSE Column50 END;

请注意，REPLACE(Column1, 'NULL', NULL)不起作用。第三个参数不接受NULL作为值。另外，您希望该表达式对诸如'Nullify'之类的值做什么？

编辑：如果需要，添加了动态脚本：

DECLARE @SQL nvarchar(MAX);
SELECT @SQL = N'UPDATE [YourTable]' + NCHAR(10) +
              STUFF((SELECT N',' + NCHAR(10) + N'SET ' + QUOTENAME(c.[name]) + N' = CASE ' + QUOTENAME(c.[name]) + N' WHEN ''NULL'' THEN NULL ELSE ' + QUOTENAME(c.[name]) + N' END'
                     FROM sys.tables t
                          JOIN sys.columns c ON t.object_id = c.object_id
                          JOIN sys.types ct ON c.user_type_id = ct.user_type_id
                     WHERE t.[name] = 'YourTable'
                     AND ct.[name] IN ('varchar','nvarchar','char','nchar')),1,2,'') + ';'
PRINT @SQL;
--EXEC sp_executesql @SQL;

Answer 3

黑客的方法是编写一个 UPDATE 语句，该语句将包含'NULL'的每个列值设置为 null，否则将其设置为原始值，例如：

UPDATE SomeTable
SET 
    Col2= case col2 when 'NULL' then NULL esle Col2 END,
    Col3= case col3 when 'NULL' then NULL esle Col3 END,
    ...
WHERE Col2 = 'NULL or Col3='NULL' or ...

这很笨拙，因为每次加载数据时都必须重复它。数据库不是Excel工作表，没有"搜索和替换所有单元格"。一开始就没有细胞。

解决此问题的正确方法是修复导入脚本，以便它将NULL字符串作为 NULL 值进行处理。

如果不能这样做，则可以向表添加一个 INSTEAD OF 触发器，以便将'NULL'字符串替换为NULL值，例如：

CREATE TRIGGER Insert_Cleanup ON dbo.SomeTable    
INSTEAD OF INSERT
AS
    SET NOCOUNT ON
INSERT INTO dbo.SomeTable(Col1, Col2, ....., ColN)
  SELECT 
      Col1, 
      case col2 when 'NULL' then NULL esle Col2 END,
      case col3 when 'NULL' then NULL esle Col3 END,
      ...
  FROM 
      INSERTED

<</div> div class="one_answers">

这应该更新数据库中所有可为空的字符列。

--Get all tables to update
DECLARE @Tables TABLE (TableName VARCHAR(MAX))
INSERT INTO @Tables
SELECT DISTINCT Table_Name FROM INFORMATION_SCHEMA.COLUMNS 
DECLARE @TableName VARCHAR(MAX)
DECLARE @SQL NVARCHAR(4000)       
--Loop though tables
WHILE EXISTS(SELECT * FROM @Tables)
BEGIN
    SELECT TOP 1 @TableName = TableName FROM @Tables
    PRINT @TableName
    SET @SQL = NULL
    --Construct the update statement using the columns from information_schema        
    SELECT @SQL = COALESCE(@SQL +',','') + '[' + COLUMN_NAME + '] = CASE WHEN CAST([' + COLUMN_NAME + '] AS VARCHAR(MAX)) = ''NULL'' THEN NULL ELSE [' + COLUMN_NAME + '] END ' 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE Table_Name = @TableName AND (select columnproperty(object_id(@TableName),COLUMN_NAME,'IsIdentity')) = 0 AND is_nullable = 'YES' AND DATA_TYPE LIKE '%char%'      
    SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL
    PRINT @SQL
    IF NOT @SQL IS NULL
    BEGIN
        --Execute the update statement
        EXECUTE sp_executesql @Statement = @SQL
    END
    ELSE
    BEGIN
        PRINT 'NOTHING TO UPDATE'
    END
    DELETE FROM @Tables WHERE @TableName = TableName
END