ISNULL() to Integer?

  • Thread starter Thread starter Jonathan Wood
  • Start date Start date
J

Jonathan Wood

Is there a SELECT syntax for returning 1 if a particular column is NULL, or
0 if the column is not NULL?

I found ISNULL(), but that returns the column if it is not NULL where I'd
want to return 1. I also found IS NULL but couldn't see a way to use that
either.

Thanks.
 
I found IIF(), which looks like that should work. (Didn't see it before
because it doesn't turn blue in the editor indicating a keyword.)
 
Thanks for that.

After further investigation, it appears IIF is yet another thing not
supported by MS SQL Server. Thanks MS.
 
IsNull should work. Perhaps you are missing the second argument?

declare @IntVal1 int
declare @IntVal2 int

set @IntVal1 = null
set @IntVal2 = 2

select IntVal1 = isnull(@IntVal1, 1), IntVal2 = isnull(@IntVal2, 1)
 
Thanks, but unless I'm missing something, this doesn't meet my requirements.

I need a single value that is either 1 or 0. IsNull() always returns the
original column value if it is not null.
 
Bleck, that will teach me to only read the first sentence. Misbah's case
statement is correct.
 
In your example, you were trying to add IIF() to IsNull(), which already
does what you want:

SELECT FirstName, Lastname, TelephoneNumber,
IsNull(TelephoneInstructions, "Any Time") as [When to Contact]
FROM db1.ContactInfo

This is functionally equivalent to

SELECT FirstName, Lastname, TelephoneNumber,
CASE WHEN TelephoneInstructions IS NULL THEN 'Any Time'
ELSE TelephoneInstructions END as [When to Contact]
FROM db1.ContactInfo

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************
Jonathan Wood said:
Actually, I found this example at
http://msdn2.microsoft.com/en-us/library/ms181765.aspx:

SELECT FirstName, Lastname, TelephoneNumber,
IIf(IsNull(TelephoneInstructions),"Any time",
TelephoneInstructions) AS [When to Contact]
FROM db1.ContactInfo

But when I do something similar I get an error about ISNULL() requiring
two arguments.

Any tips?

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

Jonathan Wood said:
I found IIF(), which looks like that should work. (Didn't see it before
because it doesn't turn blue in the editor indicating a keyword.)
Click to expand...
Click to expand...
 
You are aiming for:

SELECT FirstName, Lastname, TelephoneNumber,
CASE WHEN TelephoneInstructions IS NULL THEN 'Any Time'
ELSE TelephoneInstructions END as [When to Contact]
FROM db1.ContactInfo

Also can use

SELECT FirstName, Lastname, TelephoneNumber,
IsNull(TelephoneInstructions, "Any Time") as [When to Contact]
FROM db1.ContactInfo

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************
 
Yes, but again that doesn't meet my requirements. The result needs to be
either a 1 or a 0.

Like LIMIT, looks like MS decided there's no need to implement IIF(). CASE
works but is no where near as clean.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com


Cowboy (Gregory A. Beamer) said:
In your example, you were trying to add IIF() to IsNull(), which already
does what you want:

SELECT FirstName, Lastname, TelephoneNumber,
IsNull(TelephoneInstructions, "Any Time") as [When to Contact]
FROM db1.ContactInfo

This is functionally equivalent to

SELECT FirstName, Lastname, TelephoneNumber,
CASE WHEN TelephoneInstructions IS NULL THEN 'Any Time'
ELSE TelephoneInstructions END as [When to Contact]
FROM db1.ContactInfo

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box! |
*************************************************
Jonathan Wood said:
Actually, I found this example at
http://msdn2.microsoft.com/en-us/library/ms181765.aspx:

SELECT FirstName, Lastname, TelephoneNumber,
IIf(IsNull(TelephoneInstructions),"Any time",
TelephoneInstructions) AS [When to Contact]
FROM db1.ContactInfo

But when I do something similar I get an error about ISNULL() requiring
two arguments.

Any tips?

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

Jonathan Wood said:
I found IIF(), which looks like that should work. (Didn't see it before
because it doesn't turn blue in the editor indicating a keyword.)

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

Is there a SELECT syntax for returning 1 if a particular column is
NULL, or 0 if the column is not NULL?

I found ISNULL(), but that returns the column if it is not NULL where
I'd want to return 1. I also found IS NULL but couldn't see a way to
use that either.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top