468,765 Members | 1,747 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,765 developers. It's quick & easy.

How to change cell values from NULL to BLANK?

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?
Jul 20 '05 #1
1 9869
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.
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by zhoujie | last post: by
reply views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.