How to remove 'NULL' from results of queries SQL Server 2008
Solution 1
Use the SQL standard COALESCE
:
UPDATE my_table
SET column_1 = COALESCE(column_1,'')
, column_2 = COALESCE(column_2,'')
...
WHERE column_1 IS NULL OR
column_2 IS NULL OR
... -- avoid empty updates
;
Then use ALTER TABLE ...
to add NOT NULL
constraints to all columns that shall not have NULL to prohibit re-introducing NULL values.
Don't use , which basically is a duplication of the standard ISNULL
COALESCE
in some RDBMS - and not available in others. (Well, there are subtle differences, read the manual for details or even more detail here.)
Of course, the empty string (''
) is only valid for string types. Not for number types, for instance.
Solution 2
Use isnull function. Returns specified value if null is found in column
Select isnull(col1,'')
Comments
-
Bevan over 3 years
I have a table with 59 columns and over 17K rows. Lots of the rows have
NULL
in some of the columns.I want to remove the
NULL
so that queries return a blank (''
) rather than aNULL
.Can I run some update function that replaces all
NULL
with''
?Using SQL Server 2008R2 Management Studio.
UPDATE my_table SET column_1 = REPLACE (column_1,NULL,'')
But that would take forever to do it to all 59 columns!
What's the trick, team?
-
Bevan over 8 yearsThis is a useful feature, which I will use elsewhere, but not what I was looking for today.