Set default value in query when value is null

问题:

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

回答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...

回答2:

Use CASE:

SELECT regname, 
       regemail, 
       regphone, 
       regorg, 
       regcountry, 
       datereg, 
       CASE 
         WHEN website IS NULL THEN 'no website' 
         ELSE website 
       END AS WebSite 
FROM   regtakepart 
WHERE  reject IS NULL 

or COALESCE:

....
COALESCE(website, 'no website') AS WebSite 
....

回答3:

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

回答4:

You just use ISNULL(website, 'yourstring').

So, your query will be like:

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

回答5:

To avoid some problems to other users :

Problem 1 + Solution :

If you are planning to use the ISNULL function with an Access Database, be aware of the fact that the ISNULL function implemented in Access is differrent from the SQL Server implementation.

Access ISNULL function always returns TRUE or FALSE. So you have to use a custom IIF function around your ISNULLusage.

Problem 2 + Solution :

Moreover, if you wish to use the same field name as Alias for your column, this could lead the Access Database Engine to inform you that there is a "circular reference" to the field.

So if you need to use the same field name as Alias, you just have to add the table name before the field name. (example : You have to use RegTakePart.Website instead of simply Website). This way, you can freely use Website as Alias name for your column.

The global working (and tested) SQL query to avoid these 2 problems is the following one :

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

回答6:

Check the COALESCE function

http://msdn.microsoft.com/en-us/library/ms190349.aspx

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