Replace Empty Cells With Null Values In Large Number Of Columns

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 t

Solution 1:

Run the following query:

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

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

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

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

Solution 2:

You could do a query on syscolumns to get a list of columns, and use the results to construct your query.

select quotename(name) +' = nullif ('+ quotename(name)+','''')'from syscolumns 
where id = object_id('yourtable')

Additionally, if you write your query as

update yourtable
set
    yourcolumn=nullif(yourcolumn, ''),
    yourcolumn2=nullif(yourcolumn2, ''),
    ...    

then you can do it in a single query without a where clause

Solution 3:

I actually use Robert N's answer above daily when I'm importing flat file data sets, so I put it into a stored procedure that I could pass a table name to. It just populates a temp table with the update statements, then executes each row in the table.

    USE [master]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================-- Author:      LikeableBias-- Create date: 2016-06-27-- Description: Finds and NULLs all blank values in table where column allows nulls-- =============================================CREATEPROCEDURE [dbo].[sproc_NullBlanks] 
        @tablename NVARCHAR(MAX)
    ASBEGINSET NOCOUNT ON;
    --------Insert update statements to temp table for executionDECLARE@statementsTABLE (statement NVARCHAR(MAX))
    INSERTINTO@statements
            ( statement )
        SELECT ('UPDATE '+@tablename+' SET ['+ name +'] = NULL WHERE '+ name +' = '''';')
        FROM syscolumns
        WHERE id = OBJECT_ID(@tablename)
        AND isnullable =1;
    --------Open cursor, execute statements, then close cursorDECLARE@statement NVARCHAR(MAX)
    DECLARE cur CURSORLOCALFORSELECT statement FROM@statementsOPEN cur
    FETCH NEXT FROM cur INTO@statement
    WHILE @@FETCH_STATUS =0BEGINEXEC sys.sp_executesql @statementFETCH NEXT FROM cur INTO@statementENDCLOSE cur
    DEALLOCATE cur

    END
    GO