sql server中是否有与ISNULL相反的函数?要做的不是空的?

I have this code in my select statement

在select语句中有这段代码

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?

但是我想看看a。PolicySignedDateTime”不是null。是否有一个简单的函数可以做到这一点，而不需要使用“if”语句?

Cheers guys

欢呼的家伙

4 个解决方案

#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',

这里有一个合并表达式(虽然不是函数https://msdn.microsoft.com/en-us/library/ms190349.aspx)，并按顺序测试参数，直到发现值不为空并返回它为止。示例用法:选择COALESCE(NULL, NULL, 5)——在您的例子中返回5:COALESCE(a.PolicySignedDateTime,aq.Amount)作为“签名溢价”，

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

但....ISNULL(a。PolicySignedDateTime, aq.Amount)检查您的字段是否为null，以便获取它的值。

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

使用一个字符串+‘东西’将NULL如果字符串为空,如果为空然后使用ISNULL返回换掉,得到正确的()的返回值和检查使用NULLIF反对‘东西’,然后使用合并做你想做的事情如果是零(即原始值不是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.

尝试这段代码，并尝试不使用@text的值。

#1