Set default value in query when value is null

Question

I'm running a really simple query, however for some of the results the value in one field is null. How can I set that value to "a string" if its value is null?

Something like

SELECT RegName,
    RegEmail,
    RegPhone,
    RegOrg,
    RegCountry,
    DateReg,
    (Website IS NULL? 'no website' : Website) AS WebSite
FROM RegTakePart
WHERE Reject IS NULL

It will be running on a sql server 2005

thanks

Answer 1

Use the following:

SELECT RegName,
       RegEmail,
       RegPhone,
       RegOrg,
       RegCountry,
       DateReg,
       ISNULL(Website,'no website')  AS WebSite 
FROM   RegTakePart 
WHERE  Reject IS NULL

or as, @Lieven noted:

SELECT RegName,
       RegEmail,
       RegPhone,
       RegOrg,
       RegCountry,
       DateReg,
       COALESCE(Website,'no website')  AS WebSite 
FROM   RegTakePart 
WHERE  Reject IS NULL

The dynamic of COALESCE is that you may define more arguments, so if the first is null then get the second, if the second is null get the third etc etc...

Answer 2

As noted above, the coalesce solution is preferred. As an added benefit, you can use the coalesce against a "derived" value vs. a selected value as in:

SELECT
       {stuff},
       COALESCE( (select count(*) from tbl where {stuff} ), 0 )  AS countofstuff
FROM
       tbl
WHERE
       {something}

Using "iif" or "case" you would need to repeat the inline whereas with coalesce you do not and it allows you to avoid a "null" result in that return...