471,090 Members | 1,318 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

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 1766
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

8 posts views Thread by dmcconkey | last post: by
7 posts views Thread by John A. | last post: by
6 posts views Thread by tshad | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.