Is there a opposite function to ISNULL in sql server? To do Is not null?

I have this code in my select statement

ISNULL(a.PolicySignedDateTime,aq.Amount) AS 'Signed Premium', 

But I want to see if "a.PolicySignedDateTime" is not null. Is there a easy function to do this which does not involve using a "if" statement?

Cheers guys

9 Answers

You have to use CASE

SELECT CASE WHEN Field IS NOT NULL THEN 'something' ELSE 'something else' END 

I know is late but just in case someone else viewing this and using MSSQL 2012 or above you could use 'IIF' statement.

I guess OP don't want to use 'IF' clausule cause is "too much code syntax" to acomplish simple stuff.

An alternative also cleaner than 'IF' statement is 'IIF'. Is just an inline 'IF' simplification.

SELECT IIF(X IS NULL, 'Is null', 'Not null') 'Column Name' 

Regarding OP

SELECT IIF(a.PolicySignedDateTime IS NULL, NULL, aq.Amount) AS 'Signed Premium' 

It seems to me this would be an easy way to get the opposite.

SELECT (1-ISNULL(field));

Will give you 1 if NOT NULL and 0 if NULL instead of the 1 for NULL and 0 for NOT NULL

There is the COALESCE expression (although not function ) test the arguments in order and keep doing it until finds the value not NULL and returns it.

example usage:
SELECT COALESCE(NULL, NULL, 5)--returns 5

In your case :
COALESCE(a.PolicySignedDateTime,aq.Amount) AS 'Signed Premium',

Try this:

 SELECT CASE WHEN a.PolicySignedDateTime IS NOT NULL THEN a.PolicySignedDateTime ELSE aq.Amount FROM your joined table 

But.... ISNULL(a.PolicySignedDateTime, aq.Amount) check if your field is null, so is not null you obtain its value.

So I don't really understand because you want to use another way.

There is no opposite function but you can do it without CASE.

Use the fact that a string + 'something' will be NULL if string is NULL, and if that is null then use ISNULL to return 'somethingelse', get end of the returned value with RIGHT() and check that against 'something' using NULLIF, and then use COALESCE to do what you'd like to do if that is NULL (meaning original value is not null).

Example:

declare @text varchar(20) = 'some text or value' select COALESCE(NULLIF(RIGHT(ISNULL(@text + 'NOT', 'IS ') + 'NULL', 7), 'NOTNULL'), 'NOT NULL') 

Try this code and also try it with no value for @text.

Since we are in DBMS environment, it is possible to use set-based approach. So, assuming that table has an identifier field (id) - primary key or unique and not null, the job can be done this way:

SELECT numeric_field * count(flag_field) AS not_null_flag_numeric_value_or_zero FROM my_table GROUP BY id, numeric_field 

If value field is of character data type some char function should be used. So, practical implementation of the approach might be as below:

SELECT * INTO #temporary_table FROM (VALUES (1, 1, 111, 'string_1'), (2, NULL, 222, 'string_2') ) data_table(id, flag_field, numeric_field, character_field) ALTER TABLE #temporary_table ADD CONSTRAINT tab_pk PRIMARY KEY (id) SELECT count(flag_field) AS is_not_null, numeric_field * count(flag_field) AS numeric_value_or_zero, numeric_field * nullif(count(flag_field), 0) AS numeric_value_or_null, left(character_field, len(character_field) * count(flag_field)) AS character_value_or_empty, stuff(character_field, nullif(count(flag_field), 0), 0, '') AS character_value_or_null FROM #temporary_table GROUP BY id, numeric_field, character_field --DROP TABLE #temporary_table 

Another option to get NOT NULL sign in form of 1 is to use conversion of flag_field value to bit:

... cast(flag_field as bit) ... 

This works when conversion to bit is available for your flag_field data type and is usefull when you can consider 0-values and NULLs as same NO VALUE. Represented by NULL or 0 - as you choose:

SELECT nullif(cast(flag_field as bit), 0) AS is_not_null_or_null, isnull(cast(flag_field as bit), 0) AS is_not_null_or_zero, numeric_field * nullif(cast(flag_field as bit), 0) AS value_or_null, numeric_field * isnull(cast(flag_field as bit), 0) AS value_or_zero FROM #temporary_table 

CREATE FUNCTION ISNUL (@DATA sql_variant) RETURNS BIT AS BEGIN IF (@DATA IS NULL) RETURN 1; RETURN 0 END SELECT dbo.ISNUL(NULL) -- 1 SELECT dbo.ISNUL('123') -- 0 SELECT dbo.ISNUL(123) -- 0 SELECT dbo.ISNUL(123) ^ dbo.ISNUL(NULL), dbo.ISNUL('123') ^ dbo.ISNUL(NULL), dbo.ISNUL('123') ^ dbo.ISNUL(123), dbo.ISNUL(NULL) ^ dbo.ISNUL(NULL) -- 1,1,0,0 

Or when XOR is in focus:

CREATE FUNCTION XOR (@D1 sql_variant, @D2 sql_variant) RETURNS BIT AS BEGIN IF (@D1 IS NULL AND @D2 IS NULL) RETURN 0; IF (@D1 IS NOT NULL AND @D2 IS NOT NULL) RETURN 0; RETURN 1 END SELECT XOR(NULL,123), XOR('123',NULL), XOR(NULL,NULL), XOR('123',123) SELECT 'TRUE' where XOR(NULL,123) = 'true' SELECT 'FALSE' where XOR('123',123) = 'false' 

isnull(nullif(isnull(@Num,0),@Num),1)

0 for null, 1 for not null