how to remove "null" in each value

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to remove "null" in each value

Author

Topic

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2012-11-11 : 20:23:44

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)

17689 Posts

Posted - 2012-11-11 : 20:37:20

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
[spoiler]Time is always against us[/spoiler]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-11 : 20:45:43

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

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2012-11-11 : 22:18:09

Thank you so much jimf and khtan.
It works for me now.

Thanks, db

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources