Replace all empty character-fields in an MSSQL table with NULL

One good way is to build a dynamic SQL statement, and then run it with sp_executesql. The syntax of the sp_executesql statement is as follow: (See detail documentation of the statement on Microsoft Developer Network site)

sp_executesql [@stmt=] statement
[
  {, [@params=] N'@parameter_name data_type [OUT|OUTPUT][,...n]'} 
     {, [@param1=]'value1'[,...n]}
]

1. Declare a unicode variable for the dynamic SQL statement:

   DECLARE @sSQL nvarchar(max) = ''

2. Build your dynamic SQL statment.
   1. For example, if you wish to replace all empty character fields in table [myBlog] then the statement would be:

      SELECT
      @sSQL = @sSQL + 'UPDATE [' + TABLE_NAME + '] SET [' +
          COLUMN_NAME + '] = NULL WHERE ([' + COLUMN_NAME +
          '] = '''')' + CHAR(13)
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (TABLE_NAME='myBlog') AND (DATA_TYPE LIKE '%char%')
      

   2. Or, if you wish to replace all of them in all the tables of the current database:

      SELECT
      @sSQL = @sSQL + 'UPDATE [' + TABLE_NAME + '] SET [' +
          COLUMN_NAME + '] = NULL WHERE ([' + COLUMN_NAME +
          '] = '''')' + CHAR(13)
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (DATA_TYPE LIKE '%char%')
      

3. Execute the dynamic SQL statement.

   EXEC sp_executesql @sSQL