Turn all <NULLS> in a table into blanks?

[nkomokov]

I want to change all the NULL values into blanks to ease my manual editing of duplicates. How?

 

[gmmastros]

You probably shouldn't do this but...

You will need to know the data type for each column, and then...

For char/nchar/varchar/nvarchar
Update Table Set Field = IsNull(Field, '')
OR
Update Table Set Field = '' Where Field Is NULL

for numbers
Update table set field = IsNull(Field, 0)
OR
Update table set Field = 0 Where Field Is NULL

Unless you are very familiar with the database, you probably shouldn't do this. Generally, NULL means 'I don't know', so, by setting the value from NULL to blank you will lose information. Specifically, you won't know which fields you don't know the value for compared to those fields with truely blank data.

For example, not everyone has a middle name. If you were storing middle name, and the value is NULL, it should represent that you don't know what the middle name is. If the value is '' it should be that you know waht the middle name is, and it's blank.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Walid]

UPDATE <TableName>
SET ColName = ''
WHERE ColName IS NULL

Walid Magd

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[druer]

Your other option for doing this, and going forward would be to alter the table and have a default for the fields as either '' or 0 based on the datatypes, so that going forward NULLS will be replaced by the server for you.

 

[SQLDenis]

you can also make the fields not nullable after you are done updating so that in the future you won't have to maintain this

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/