How to change cell values from NULL to BLANK?

[Scott]

In the result of a SELECT statement, how can you change cell values
from NULL to BLANK?
The following does NOT do it:

SET fieldname = ' '
WHERE fieldname IS NULL

Also, for colums with a DATE data type, I want to change 0000-00-00 to
BLANK.
For for colums with a numeric data type such as DOUBLE, I want to
change 00.00 to BLANK.

Again, the above doesn't work. How can I blank those fields?

[Bill Karwin]

Scott wrote:

In the result of a SELECT statement, how can you change cell values
from NULL to BLANK?
The following does NOT do it:

SET fieldname = ' '
WHERE fieldname IS NULL
That syntax is for the UPDATE statement, not the SELECT statement. Do
you want to change the values as they are stored in the database, or do
you want to change them dynamically only when you do a SELECT?

Here's the solution for both cases:

UPDATE myTable SET fieldname = ' ' WHERE fieldname IS NULL;

SELECT COALESCE(fieldname, ' ') FROM myTable;
Also, for colums with a DATE data type, I want to change 0000-00-00 to
BLANK.
For for colums with a numeric data type such as DOUBLE, I want to
change 00.00 to BLANK.

A blank is not a legal value to store in date and numeric columns. You
can change them to store a NULL state:

UPDATE myTable SET myDateField = NULL WHERE myDateField = '0000-00-00';
UPDATE myTable SET myNumField = NULL WHERE myNumField = '0.0';

Then you can use something like the SELECT COALESCE... example above.

Bill K.