Is there a opposite function to ISNULL in sql serv

问题:

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

回答1:

You have to use CASE

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

回答2:

There is the COALESCE expression (although not function https://msdn.microsoft.com/en-us/library/ms190349.aspx) 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',

回答3:

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.

回答4:

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.

回答5:

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

回答6:

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'