Convert Empty Strings to Nulls in SQL


Overview

Often when importing data in SQL, you end up with cells that contain empty strings, spaces only or the like. In order to keep a clean database, I like to convert these to NULL values. In case you would like to do this as well, I’ve posted my script that iterates all columns in a temp table and converts empty cells to NULL values.

-- Declare the cursor as read only and select the specified temp table's field names.
Declare objESNCur CURSOR
Read_Only
FOR     Select Column_Name
From    TempDB.Information_Schema.Columns
Where   Object_ID('tempdb..#TableToClean') = Object_ID('tempdb..'+TABLE_NAME)
-- Declare a variable to contain the field name for the current iteration.
Declare @Column_Name nvarchar(255)
-- Open the cursor
Open    objESNCur
-- Fetch the first field name into the variable.
Fetch   Next
From    objESNCur
Into    @Column_Name
-- Loop while there are no errors and there are still rows to be fetched.
While (@@fetch_status <> -1)
Begin
 -- Loop while there are still rows to be fetched.
 If (@@fetch_status <> -2)
 Begin
  -- Make sure field name isn't a field I don't want to cleanup.
  If (@Column_Name != 'NotThisField')
  Begin
   -- Declare a temporary variable to house a dynamic SQL statement.
   Declare @ESNSQL nvarchar(4000)
   -- Set the SQL variable to update blank fields to NULLs.
   Set     @ESNSQL = 'Update #TableToClean
                      Set ' + @Column_Name + ' = Null
                      Where LTrim(RTrim(' + @Column_Name + ')) = '''''
   -- Execute the dynamic SQL statement.
   Exec    sp_executesql @ESNSQL
  End
 End
 -- Fetch the next row into the field name variable.
 Fetch  Next
 From   objESNCur
 Into   @Column_Name
End
-- Close the cursor.
Close   objESNCur
-- Deallocate the cursor resources.
Deallocate objESNCur

If this has been of help to you, please feel free to leave me a comment.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s