Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to remove "null" in each value
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

50 Posts

Posted - 11/11/2012 :  20:23:44  Show Profile  Reply with Quote

I wrote following syntax to eliminate "NULL" in the value of each field so when paste the result into excel, the "NULL" doesn't show up in each cell in MS EXCEL.
But for some reason, my logic ended up replacing all value to 0 in some field and all empty value in some field.
Could you please teach me how to replace "NULL" as empty value ?

update my_table set field1 = '', field2 = '' where (field1 is null or field2 is null )

Thank you

In (Som, Ni, Yak)

17689 Posts

Posted - 11/11/2012 :  20:37:20  Show Profile  Reply with Quote
the problem is in the WHERE condition
(field1 is null or field2 is null )

Even if field1 is NULL but field2 is not, you are also replacing BOTH field1 and field2 with empty space.

Split into 2 update query or use ISNULL() or COALESCE()

SET field1 = ISNULL(field1, ''),
    field2 = ISNULL(field2, '')

Time is always against us

Go to Top of Page

Flowing Fount of Yak Knowledge

2875 Posts

Posted - 11/11/2012 :  20:45:43  Show Profile  Reply with Quote
This is a problem of how Excel is interpreting the null value. If you want "NULL" to show up in your Excel, then you'd have to use the literal "NULL" in your statement
update my_table set field1 = 'NULL', field2 = 'NULL' where (field1 is null or field2 is null ). Be careful of data types. In SQL, '' isn't a valid value for a numeric, but Excel
just might accept it and turn it in to a 0. Also, '' and null are different. In your query, field1 and field2 will be set to '' where either field1 or field2 is null. So maybe

UPDATE my_table
SET field1 = ISNULL(field1,'NULL')
,field 2= ISNULL(field2,'NULL')


Everyday I learn something that somebody else already knew
Go to Top of Page

Yak Posting Veteran

50 Posts

Posted - 11/11/2012 :  22:18:09  Show Profile  Reply with Quote
Thank you so much jimf and khtan.
It works for me now.

Thanks, db
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000