asked on

Easiest way to go through database and convert empty strings to NULL

Hello all. I have a database that has a lot of data in it already,and just realized that some of the empty fields are zero-length strings and some are NULL values because of some of my code in my ASP pages. I have corrected my code, but now need to fix the data that is already in the database. What is the easiest way to go though all my tables and replace all empty strings with NULL values?

Thanks in advance,

Mike

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

OP_Zaharin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ambidextrous

Mike - I use a stored procedure to do this in my databases all the time.

exec dbo.sp_FixTableBlankValues 'dbo','tblToFix','MYDataBase'

Create Procedure dbo.[sp_FixTableBlankValues]
  @Schema as nvarChar(255)
 ,@Table as nvarchar(255)
 ,@DBName as nVarChar(255)
AS

Declare
  @i as Int
 ,@j as Int
 ,@SQL as nVarChar(Max)
 ,@CurColumn as nVarChar(255)

If Object_ID('TempDB..#Vars') Is Not Null
  Drop Table #Vars
Create Table #Vars (
  VarName VarChar(25)
 ,VarValue VarChar(255)
 ,Primary Key Clustered (
    VarName
  )
)

SET @SQL = '
Insert Into #Vars
SELECT ''MaxOrd'',Max(Ordinal_Position) FROM ' + @DBName + '.Information_Schema.Columns
          WHERE Table_Name = ''' + @Table + ''' AND Table_Schema = ''' + @Schema + ''''

Exec sp_ExecuteSQL @SQL

SET @i = 1
SET @j = (SELECT Cast(VarValue as Int) FROM #Vars WHERE VarName = 'MaxOrd')

WHILE @i <= Coalesce(@j,0)
  BEGIN

    SET @SQL = '
    Insert Into #Vars
    SELECT ' + Cast(@i as VarChar) + ',Column_Name
    FROM ' + @DBName + '.Information_Schema.Columns
    WHERE Table_Name = ''' + @Table + ''' AND Table_Schema = ''' + @Schema + ''' AND Ordinal_Position = ' + Cast(@i as VarChar)

    Exec sp_ExecuteSQL @SQL

    SET @CurColumn = '[' + (SELECT VarValue FROM #Vars WHERE VarName = Cast(@i as VarChar)) + ']'

    SET @SQL = '
    UPDATE ' + @DBName + '.' + @Schema + '.' + @Table + '
    SET ' + @CurColumn + ' = Null
    WHERE Cast(' + @CurColumn + ' as VarChar) = '''''

    Print @SQL
    Exec sp_ExecuteSQL @SQL

    SET @i = @i + 1

  END

Drop Table #Vars

Open in new window

OP_Zaharin

- there is no direct function for setting multiple columns to NULL without specifying the columnname however you can generate the UPDATE statements list using the following SQL.
- this works on SQL Server but you can use the same concept on other database in getting the list of tablename and columnname of text, char and varchar datatype to create a list of UPDATE statement:

SELECT 'UPDATE ' + o.name + ' SET ' + c.name + ' = NULL WHERE LEN(LTRIM(RTRIM(' +
c.name + '))) = 0'
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE o.xtype = 'U'
AND (t.name like '%char' or t.name like '%text')

tmss_it_dept

ASKER

Thanks guys. I only had 3 tables that really fell into this category, so the solution provided by matthewspatrick and OP Zaharin was the easiest for me to do. Also OP Zaharin, the last comment you gave code to generate the update statenments, when I tried that one, it said something about a Syntax error in FROM clause. It is highlighting the first JOIN statement. Will this work in Access?

Thanks again,

Mike

OP_Zaharin

- no it won't work in Access. you need to do it via your system code such as ASP or VB. here is one: http://vbcity.com/forums/t/154628.aspx