sql null's

nel · November 2007

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 · November 2007

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 · November 2007

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 · November 2007

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 · November 2007

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 · November 2007

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 · November 2007

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

sql null's

Comments