Null

Question

I want to update all columns in a table with a blank string where there is NULL.

 

---

Regards, Muhammad Bilal.

Answer 1

This is small example for one column update tableName set column1 = '' where column1 is null

---

With kind regards
Krystian Zieja
http://www.projectnenvision.com
Follow me on twitter
My Blog

Answer 2

update yourTable
set col1 = coalesce(col1, ''),
     col2 = coalesce(col2, ''),
...
     colN = coalesce(colN, '')

But wait, there's more!

This might produce the results you expect if the columns are not a character data type.  For instance, if you do this with a numeric column, you will load zero into the column.  If you do this with a datetime data type you will load the date '1/1/1900' into the column.

What is your reason for doing this?  Also, do you understand that NULL in SQL does NOT mean ''!

 

Answer 3

Only if the data type is one of the string type (char / varchar / ...). You could use ISNULL function, like in:

update T
set c1 = isnull(c1, ''), c2 = isnull(c2, ''), ...

This will update the column with its own value if it is not null. Keep in mind that this statement will be touching all rows. You can filter to just touch rows were any of the string columns is null by adding the "where" clause.

...
where c1 is null or c2 is null or ...

 

 

---

AMB

Some guidelines for posting questions...

Answer 4

try this

UPDATE TABLE TABLE1 SET COLUMN1=ISNULL(COLUMN1,''),  COLUMN2=ISNULL(COLUMN2,''),
 COLUMN3=ISNULL(COLUMN3,'')

 

---

Bipin. P | ERNST & YOUNG GSS