如果在 T-SQL 中记录为 NULL，如何替换字符串

How to Substitute a String if record is NULL in T-SQL

提问by dvanaria

I'm writing a T-SQL report that shows the number of accounts that are in different statuses for different customers. The report results in something like:

我正在编写一个 T-SQL 报告，显示不同客户处于不同状态的帐户数量。报告结果如下：

Customer1    NoService        7
Customer1    IncompleteOrder  13
Customer1    NULL             9
Customer2    NoService        12
Customer2    Available        19
Customer2    NULL             3
...

The 'NULL' status is valid data, but instead of displaying NULL, I want to display "Pending". Here is my SQL so far:

'NULL' 状态是有效数据，但我想显示“Pending”而不是显示 NULL。到目前为止，这是我的 SQL：

USE cdwCSP;
SELECT
   sr.sales_region_name   AS SalesRegion
   , micv.value
   , COUNT(sr.sales_region_name)
FROM prospect p
   LEFT JOIN sales_region sr
     ON p.salesRegionId = sr.sales_region_number
   LEFT JOIN prospectOrder po
     ON po.prospectId = p.prospectId
   LEFT JOIN wo
     ON wo.prospectId = p.prospectId
   LEFT JOIN woTray wot
     ON wot.woId = wo.woId
   LEFT JOIN miscInformationCustomerCategory micc
     ON micc.prospectId = p.prospectId
   LEFT JOIN miscInformationCustomerValues micv
     ON micv.miscInformationCustomerCategoryId = micc.miscInformationCustomerCategoryId
   LEFT JOIN miscInformationCategory mic
     ON micc.miscInformationCategoryId = mic.miscInformationCategoryId
WHERE wot.dateOut IS NULL
     AND mic.categoryName LIKE '%Serviceability%'
GROUP BY sr.sales_region_name, micv.value
ORDER BY sr.sales_region_name, micv.value;

Any help would be appreciated, I'm still learning T-SQL so this might be an easy question to answer.

任何帮助将不胜感激，我仍在学习 T-SQL，所以这可能是一个容易回答的问题。

回答by Martin Smith

You can use COALESCEor ISNULL. The former is standard and returns the first NOT NULLargument (or NULLif all arguments are NULL)

您可以使用COALESCE或ISNULL。前者是标准的并返回第一个NOT NULL参数（或者NULL如果所有参数都是NULL）

SELECT COALESCE(micv.value,'Pending') as value

ISNULLis restricted to only 2 arguments but is more efficient in SQL Server if the first value to be tested is expensive to evaluate (e.g. a subquery).

ISNULL仅限于 2 个参数，但如果要测试的第一个值的评估成本很高（例如子查询），则在 SQL Server 中效率更高。

One potential "gotcha" with ISNULLto be aware of is that it returns the datatype of the first parameter so if the string to be substituted is longer than the column datatype would allow you will need a cast.

ISNULL需要注意的一个潜在“问题”是它返回第一个参数的数据类型，因此如果要替换的字符串比列数据类型长，您将需要强制转换。

E.g.

例如

CREATE TABLE T(C VARCHAR(3) NULL);

INSERT T VALUES (NULL);

SELECT ISNULL(C,'Unknown')
FROM T

Would return Unk

会回来 Unk

But ISNULL(CAST(C as VARCHAR(7)),'Unknown')or COALESCEwould both work as desired.

但是ISNULL(CAST(C as VARCHAR(7)),'Unknown')orCOALESCE两者都可以按需要工作。

回答by Praneeth

you can also use ISNULL('value', 'replacewithvalue')

你也可以使用 ISNULL('value', 'replacewithvalue')

回答by apacay

SELECT
   sr.sales_region_name   AS SalesRegion
   , ISNULL(micv.value,'Pending')
   , COUNT(sr.sales_region_name)
FROM prospect p
--(...)

Go check ISNULLfor further info.

去检查ISNULL以获取更多信息。