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

Thanks everyone I'll accept the answer as soon as it will allow me — andrew slaughter
– andrew slaughter, Commented Oct 9, 2013 at 10:05

Giannis Paraskevopoulos · Accepted Answer · 2013-10-09 10:01:33Z

45

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

edited Oct 9, 2013 at 10:01

answered Oct 9, 2013 at 10:00

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

d.raev Over a year ago

take with ISNULL if you migrate to MySQL. The sintax there is different w3schools.com/sql/sql_isnull.asp

GarethD Over a year ago

Not relevant to this example, but still noteworthy, the downside of COALESCE is that it expands to a case statement, so COALESCE((SELECT Col FROM T), 'No Value'), expands to CASE WHEN (SELECT Col FROM T) IS NOT NULL THEN (SELECT Something FROM Table) ELSE 'No Value' END meaning the subquery is executed twice if the result is not null. ISNULL does not exhibit this behaviour. An upside of coalesce is that it is in the ANSII Standard, ISNULL() is not.

T. Baum · Accepted Answer · 2016-04-28 14:36:15Z

11

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

edited Apr 28, 2016 at 14:36

answered Apr 28, 2016 at 14:12

T. Baum

1231 silver badge5 bronze badges

1 Comment

Hakan Fıstık Over a year ago

Wrapping the first argument of the COALESCE with () is very important, or the query will not compile. Thank you for this

Tim Schmelter · Accepted Answer · 2013-10-09 10:00:50Z

9

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

answered Oct 9, 2013 at 10:00

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Comments

Radu Gheorghiu · Accepted Answer · 2013-10-09 10:00:30Z

2

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

answered Oct 9, 2013 at 10:00

Radu Gheorghiu

20.6k16 gold badges78 silver badges113 bronze badges

1 Comment

Radu Gheorghiu Over a year ago

No matter what answer you choose, they will all work. COALESCE and ISNULL are both solutions.

AlexLaforge · Accepted Answer · 2016-10-15 09:12:49Z

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

Lluis Martinez · Accepted Answer · 2013-10-09 10:02:03Z

0

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

answered Oct 9, 2013 at 10:02

Lluis Martinez

1,9848 gold badges28 silver badges42 bronze badges

Collectives™ on Stack Overflow

Set default value in query when value is null

6 Answers 6

2 Comments

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related