在SQL Server 2012中使用COALESCE()函数

I have the below query where I'm using the COALESCE() function in SQL. I want my query to return the text 'No Notes Maintained' if it does not finds anything in the NOTES column of the LOOKUP_TABLE. The query is returning blank when it does not finds any value, and not the desired value.

在下面的查询中，我使用SQL中的COALESCE()函数。如果我的查询在LOOKUP_TABLE的Notes列中没有找到任何内容，我希望它返回文本“No Notes维护”。当查询没有找到任何值(而不是期望的值)时，它将返回空。

SELECT 
    COALESCE(CONVERT(NVARCHAR(max), NOTES),'No Notes Maintained') AS NOTES 
FROM
    LOOKUP_TABLE LT
INNER JOIN 
    LOOKUP_VALUE LV ON LT.ID = LV.ID

The datatype of the NOTES column is varcharand it could contain a NULL, blank value or any text string, like - This is a note.

NOTES列的数据类型是varcharand，它可以包含空值、空白值或任何文本字符串，比如——这是一个注释。

Any help would be greatly appreciated.

如有任何帮助，我们将不胜感激。

Thanks

谢谢

5 个解决方案

#1

Why do you apply CONVERT(NVARCHAR(max)?

为什么要申请皈依(NVARCHAR(max))?

This will return what you need:

这将返回您所需要的:

CASE WHEN NOTES <> '' THEN NOTES ELSE 'No Notes Maintained' END) AS NOTES

NOTES <> '' covers NULLs, empty strings and blanks.

NOTES <> "包含空字符串、空字符串和空格。

#2

As the comments have suggested, if you have whitespace in the NOTES column, then COALESCE won't replace the value, because it is not null. If you want to treat NULL and space as the same thing, i.e. a logical missing value, then try the following query:

正如注释所建议的，如果在NOTES列中有空格，那么COALESCE不会替换这个值，因为它不是null。如果您想将NULL和space视为相同的东西，即一个逻辑缺失值，那么请尝试以下查询:

SELECT
    CONVERT(NVARCHAR(max),
        CASE WHEN COALESCE(REPLACE(NOTES, ' ', ''), '') = ''
             THEN 'No Notes Maintained'
             ELSE NOTES END) AS NOTESNOTES 
FROM LOOKUP_TABLE LT
INNER JOIN LOOKUP_VALUE LV
    ON LT.ID = LV.ID;

#3

Try this should help:

试试这应该有助于:

SELECT CASE WHEN ISNULL(CONVERT(NVARCHAR(max), NOTES),'')!='' THEN CONVERT(NVARCHAR(max), NOTES) ELSE'No Notes Maintained' END AS NOTES 
FROM   LOOKUP_TABLE LT
  INNER JOIN LOOKUP_VALUE LV ON LT.ID = LV.ID

#4

Given that the note field can be NULL, blank or empty string, the following code should return your desired results:

考虑到note字段可以为空、空或空字符串，下面的代码应该返回您想要的结果:

SELECT     
    CONVERT( NVARCHAR(MAX), 
    CASE 
        WHEN ISNULL(LTRIM(RTRIM(NOTES), '')) = '' THEN 'No Notes Maintained'
        ELSE NOTES
    END) AS NOTES 
FROM
    LOOKUP_TABLE LT
INNER JOIN 
    LOOKUP_VALUE LV ON LT.ID = LV.ID

#5

Try This.

试试这个。

SELECT ISNULL(NULLIF(LTRIM(RTRIM(column_name)), ''), 'Display Text')
FROM TableName

Explanation: You can remove the whitespace from both sides of a string using LTRIM and RTRIM together. When a string has only whitespaces, this will return an empty string value (Not a null value).

说明:您可以使用LTRIM和RTRIM从字符串的两边删除空白。当一个字符串只有空格时，它将返回一个空字符串值(不是空值)。

Then using NULLIF() function, you can compare two expressions. If expr1 and expr2 are equal, the NULLIF() function returns NULL. Otherwise, it returns expr1. In this case, when the string is an empty value, it will return NULL since it's being compared to an empty string.

然后使用NULLIF()函数，您可以比较两个表达式。如果expr1和expr2相等，则NULLIF()函数返回NULL。否则,它将返回expr1。在这种情况下，当字符串是空值时，它将返回NULL，因为它正在与空字符串进行比较。

Finally, you can return an alternative value when an expression is NULL using ISNULL() function.

最后，可以使用ISNULL()函数返回一个替代值。

SELECT 
    ISNULL(NULLIF(LTRIM(RTRIM(LT.NOTES)), ''), 'No Notes Maintained') 
FROM
    LOOKUP_TABLE LT
INNER JOIN 
    LOOKUP_VALUE LV ON LT.ID = LV.ID

OR you can do something like this...

或者你可以这样做…

SELECT 
CASE
    WHEN Len(column_name) > 0 THEN column_name -- Covers NULL, Empty Strings and Whitespaces.
    ELSE 'Display Text'
END
FROM TableName;

In your case, the query will be like this.

在您的例子中，查询将是这样的。

SELECT 
CASE
    WHEN Len(LT.NOTES) > 0 THEN LT.NOTES
    ELSE 'No Notes Maintained'
END
FROM 
    LOOKUP_TABLE LT
INNER JOIN 
    LOOKUP_VALUE LV ON LT.ID = LV.ID

#1