Posted by BF on 12/19/06 11:37

Great,

Thanks a lot, with the query I can create the new script much and much
easier.

I replaced the o.name with the tables which I new they had the problem
and now I have have all columns to replace the values.

Thanks a lot.

Regards, Bob

Erland Sommarskog schreef:

> BF (bob@faessen.net) writes:
> > I have some tables where I import data in, lots of field have gotten a
> > NULL value which the application can not handle.
> >
> > Now can I replace each NULL value with '' in a columns with:
> > update <table> set [<column>] = '' where [<column>] IS NULL
> >
> > But because there are lots of columns this is pretty much work, also
> > there are multiple tables.
> >
> > Is there an easy way to replace all NULL values in all columns in a
> > table?
>
> First of all, that operation would only be possible with columns
> that hold character data. For numeric and datetime columns there
> is rarely any good replacement for NULL values. So, unless, your
> database only has nullable character columns, you need to fix the
> application to handle NULL values anyway.
>
> No, there is no direct function for setting many columns to NULL. You
> need to have an UPDATE statement for each table, and one that lists
> all columns that should be set to NULL. The good news is that you
> can generate the statements:
>
> SELECT 'UPDATE ' + o.name + ' SET ' + c.name + ' = '''' WHERE ' +
> c.name + ' IS NULL'
> 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')
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]