The bane of my existence, blank values that should be NULL in a database. This happens frequently when importing Excel/Flat files particularly within SQL Server. Running hundreds of UPDATE statements isn’t fun, so I use a Stored Procedure with a CURSOR to take care of it.
CREATE PROCEDURE [dbo].[usp_getRidOfTableBlanks](@tableName nvarchar(50)) AS DECLARE @colName varchar(255) DECLARE @sql varchar(4000) DECLARE Table_Cursor CURSOR FOR select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tableName OPEN Table_Cursor; FETCH NEXT FROM Table_Cursor INTO @colName WHILE @@FETCH_STATUS = 0 BEGIN set @sql = 'UPDATE ' + @tableName + ' SET [' + @colName + '] = NULL WHERE [' + @colName + '] = '''';'; print(@sql) exec (@sql) FETCH NEXT FROM Table_Cursor INTO @colName END; CLOSE Table_Cursor; DEALLOCATE Table_Cursor; GO
Just run an exec with the table name and voila, beautiful UPDATE statements while you sit back and wish this didn’t happen.
exec usp_getRidOfTableBlanks 'MyTable'