quickest way to update a table to set blank values to NULLs?

Question

I have a table and there are blank values in several columns, scattered all over the place.

I want to replace '' with NULL.

What's the quickest way to do this? Is there a trick I'm not aware of?

What are you aware of? It would be helpful if you included a query in your question. And, there probably isn't a trick. — Gordon Linoff, Mar 11 '14 at 22:05
dognose, I know how to update a table. trust me. I'm asking if there's a work around instead of updating every single column (there are 30 columns in the table). — JJ., Mar 11 '14 at 22:19
If you don't want to write the script manually, use the `information_schema.columns` view to build the sql for you. — David, Mar 11 '14 at 22:24
Here is something which might interest you http://stackoverflow.com/questions/3175118/update-all-null-fields-mysql — Mihai, Mar 11 '14 at 22:28

score 2 · Answer 1 · answered Mar 11 '14 at 22:20

2

update <table name> set <column 1> = case when <column 1> = '' then null else <column 1> end, <column 2> = case when <column 2> = '' then null else <column 2> end

you can add as many lines as you have columns. No need for a where clause (unless you have massive amounts of data - then you may want to add a where clause that limits it to rows that have empty values in each of the columns you are checking)

answered Mar 11 '14 at 22:20

JebaDaHut

541
5
11

Thanks. This is what I already have. I figured I would ask to see if there's an easier way to do this that I wasnt aware of. I gotta do this 30 times... – JJ. Mar 11 '14 at 22:24

score 1 · Answer 2 · answered Mar 11 '14 at 22:28

If you have a lot of columns and you don't want to write the SQL manually, you can use the Information_Schema.Columns view to generate the SQL for you...

DECLARE @Table AS Varchar(100)
SET @Table = 'Your Table'

SELECT 'UPDATE ' + @Table + ' SET ' + QUOTENAME(Column_Name)
      + ' = NULL WHERE ' + QUOTENAME(Column_Name) + ' = '''''
FROM Information_Schema.Columns
WHERE Table_Name = @Table
AND Data_Type IN ( 'varchar', 'nvarchar' )

Then just copy the result set and run it in a new query window...

score 0 · Accepted Answer · answered Mar 11 '14 at 22:53

I did it like this:

DECLARE @ColumnNumber INT
DECLARE @FullColumnName VARCHAR(50)
DECLARE @SQL NVARCHAR(500)

SET @ColumnNumber = 0

WHILE (@ColumnNumber <= 30)
      BEGIN
            SET @FullColumnName = 'Column' + CAST(@ColumnNumber AS VARCHAR(10))

            SET @SQL = 'UPDATE  [].[].[] SET ' + @FullColumnName + ' = NULL WHERE ' + @FullColumnName + ' = '''''

            EXECUTE sp_executesql
                @SQL;

            SET @ColumnNumber = @ColumnNumber + 1
      END

score 0 · Answer 4 · answered Jun 19 '22 at 16:08

This expands on JJ.'s answer. I had a table where there were there were more columns than I cared to count and I wanted to make sure that every column that had blanks were converted to nulls.

DECLARE @tableName VARCHAR(50) = 'MyTable'
DECLARE @colIndex INT = 0
DECLARE @colName VARCHAR(50)
DECLARE @sql NVARCHAR(MAX)
DECLARE @maxColCount INT = (SELECT COUNT(COLUMN_NAME)
                            FROM INFORMATION_SCHEMA.COLUMNS
                            WHERE TABLE_NAME = @tableName)

WHILE (@colIndex <= @maxColCount)
    BEGIN
        SELECT @colName = COLUMN_NAME
        FROM    INFORMATION_SCHEMA.COLUMNS
        WHERE   TABLE_NAME = @tableName AND ORDINAL_POSITION = @colIndex
        SET @sql = 'UPDATE [dbo].[' + @tableName + '] SET ' + @colName + ' = NULL WHERE ' + @colName + ' = '''''
        EXEC sp_executesql @sql
        PRINT('Updated column ' + @colName)
        SET @colIndex = @colIndex + 1
    END

quickest way to update a table to set blank values to NULLs?

4 Answers4