sql null's

nel

hi there,

im writing a query which produces a lot of null's on a test mysql db ive got going. i was just wondering (for learning purposes) how you replace the blank null spaces with a word like null or any other word for that matter?

JDMurray

If the data type of the field is a string or varchar, set the default value of the field to the literal string "NULL". The DB NULL value is used to indicate that a field has never been initialized.

BeaverC32

I am assuming that null values are already in the database, and you simply want to alter the output in your SELECT statement. If this is the case, use the COALESCE function.

Example:

Select FirstName, LastName, (COALESCE(MiddleName, "No Middle Name")
FROM Employee

In this example, COALESCE takes the first argument, "MiddleName", and outputs that value. If there is no value (aka NULL), it outputs the second argument "No Middle Name".

nel

ive tried that coalesce and it doesnt seem to work, the output has multiple fields which have null values and simply have nothing written in the box.
Does coalesce work on all dbms's?

JDMurray

nel wrote:

Does coalesce work on all dbms's?

Not all DBMS support COALESCE, but it looks like MySQL supports the COALESCE function: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

You can try using ISNULL in a case statement as an alternative.

BeaverC32

Whoops, sorry! I didn't read your post carefully enough; I'm only familiar with SQL Server, not mySQL. I would double-check the syntax to make sure the format I originally gave in the example translates over to mySQL.

blargoe

I haven't messed with queries in a long time, but isn't it just a simple update statement? Like

UPDATE Customers SET Address = 'textstring" where Address is NULL