如何将表中的所有空字符串字段更新为空? [英] How can I update all empty string fields in a table to be null?

问题描述

我有一个包含 15 列(其中 10 列是字符串值)和大约 50000 行的表格.
它包含很多空字符串值......我搜索是否有一个查询，我可以为它传递一个表名来迭代所有值，如果它等于空，则将其更新为 NULL ..

I have a table with 15 columns ( 10 of them are string value) and about 50000 rows.
It contain a lot empty string value ... I search if is there a query that I can pass a table name for it to iterate all values and if it equal empty then update it to NULL ..

推荐答案

这是一种基于表格的简单方法.只需将表名传递给 proc.您还可以创建一个姊妹 proc 来循环思想表名称，并在 while 循环内调用此 proc 以处理循环逻辑中的每个表.

this is a simple way to do it based on table. just pass the proc the table names. you can also make a sister proc to loop thought table names and call this proc inside the while loop to work on each table in your loop logic.

CREATE PROC setNullFields 
(@TableName NVARCHAR(100))        
AS    

CREATE TABLE #FieldNames    
(    
pk INT IDENTITY(1, 1) ,    
Field NVARCHAR(1000) NULL    
);    

INSERT INTO #FieldNames    
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName 

DECLARE @maxPK INT;    
SELECT @maxPK = MAX(PK) FROM #FieldNames    

DECLARE @pk INT;    
SET @pk = 1    

DECLARE @dynSQL NVARCHAR(1000) 

WHILE @pk <= @maxPK    
BEGIN    

 DECLARE @CurrFieldName NVARCHAR(100);    
 SET @CurrFieldName = (SELECT Field FROM #FieldNames WHERE PK = @pk)    

    -- update the field to null here:

    SET @dynSQL = 'UPDATE ' + @TableName + ' SET ' + @CurrFieldName + ' = NULLIF('+ @CurrFieldName+ ', '''' )' 
    EXEC (@dynSQL)

 SELECT @pk = @pk + 1    
END    

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