470,810 Members | 893 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,810 developers. It's quick & easy.

Replacing NULL value in multiple columns in a table

BF
Hi,

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 <tableset [<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?

Thanks in Advance
Bob

Dec 18 '06 #1
2 23889
BF (bo*@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 <tableset [<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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 18 '06 #2
BF
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 (bo*@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 <tableset [<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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 19 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

26 posts views Thread by Agoston Bejo | last post: by
10 posts views Thread by Bodza Bodza | last post: by
3 posts views Thread by A.V.C. | last post: by
9 posts views Thread by John Sidney-Woollett | last post: by
35 posts views Thread by jacob navia | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.