3

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 a NULL.

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bevan
  • 566
  • 4
  • 7
  • 19

3 Answers3

7

Use isnull function. Returns specified value if null is found in column

Select isnull(col1,'')
Akshey Bhat
  • 8,227
  • 1
  • 20
  • 20
  • This is a useful feature, which I will use elsewhere, but not what I was looking for today. – Bevan Dec 11 '15 at 02:56
7

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 ISNULL, which basically is a duplication of the standard 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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

Try this one:

UPDATE yourtable SET column1 = ' '  WHERE column1 = NULL
slavoo
  • 5,798
  • 64
  • 37
  • 39
Harshal Shendre
  • 31
  • 2