SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

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

dbonneau
Starting Member

42 Posts
Posted - 11/11/2012 :  20:23:44  Show Profile  Reply with Quote
Hi,

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

khtan
In (Som, Ni, Yak)

Singapore
16769 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, '')


KH
Time is always against us
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 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')

Jim

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

dbonneau
Starting Member

42 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000