How do I prevent NULLs in my database from mucking up my HTML?

Many people have come across the issue where they're using a recordset to populate a table, and someone goofed up and stored <NULL> values in the database, so it wrecks the formatting of the table. 
 
First, you should consider not allowing NULLs into your database in the first place; see Article #2073 for more info. 
 
Now, let's say you can't get around using NULLs (due to pointy-haird boss syndrome, or whatever else)... you can do one of two things: 

1. test for null values at runtime, or
2. replace null values in the query itself

I prefer (2), but I will provide examples of both. 
 
Here is an example of using VBScript to get rid of NULL values *after* they've come out of the database, but before displaying them on the screen. 
 

<%      ' ...      do while not rs.eof          cCol = rs("column_which_may_contain_nulls")          if len(cCol)=0 then cCol = "&nbsp;"          response.write "<td>" & cCol & "</td>"          rs.movenext      loop      ' ...  %>

 
Manas Tungare adds that there is an even quicker solution to this. By appending a blank string to the end of the result, you implicitly force the value to become NOT null: 
 

<%      ' ...      do while not rs.eof          cCol = rs("column_which_may_contain_nulls") & ""          response.write "<td>" & cCol & "</td>"          rs.movenext      loop      ' ...  %>

 
Here is an example of using SQL (both in Access and in SQL Server) to replace these NULL values for you, taking care of the problem at the data level (because it *is* a data problem): 
 

/* For Access: */        SELECT IIF(ISNULL(column),' ',column) FROM Table          /* For SQL Server: */        SELECT ISNULL(column,' ') FROM Table            -- or, more ANSI-compliant:        SELECT COALESCE(column,' ') FROM Table

 
(FWIW, I definitely like SQL Server's syntax better.) 
 
In any case, I still strongly suggest you avoid NULLs in your database. But if you absolutely must have them, I hope that these solutions help alleviate some of the problems they cause.