MSSQL Server 处理Null

Null，在T-SQL中是个很诡异的内容，它的处理方式不按常规来走，以至于我们在处理数据的时候，经常会变的手足无措，这里讨论下，null与字符类型拼接的情况。

以下语句：

SELECT custid, country, region, city,
  country + N',' + region + N',' + city AS location
FROM Sales.Customers;

执行结果：

custid      country         region city            location
----------- --------------- ------ --------------- -------------------
1           Germany         NULL   Berlin          NULL
2           Mexico          NULL   México D.F.     NULL
3           Mexico          NULL   México D.F.     NULL
4           UK              NULL   London          NULL
5           Sweden          NULL   Luleå           NULL
6           Germany         NULL   Mannheim        NULL
7           France          NULL   Strasbourg      NULL
8           Spain           NULL   Madrid          NULL
9           France          NULL   Marseille       NULL
10          Canada          BC     Tsawassen       Canada,BC,Tsawassen
11          UK              NULL   London          NULL
12          Argentina       NULL   Buenos Aires    NULL
13          Mexico          NULL   México D.F.     NULL
14          Switzerland     NULL   Bern            NULL
15          Brazil          SP     Sao Paulo       Brazil,SP,Sao Paulo
16          UK              NULL   London          NULL
17          Germany         NULL   Aachen          NULL
18          France          NULL   Nantes          NULL
19          UK              NULL   London          NULL
20          Austria         NULL   Graz            NULL
...

(91 row(s) affected)

location列很多为null这是因为country + N',' + region + N',' + city中的三个字段很多是null，此时如果我们希望值为null的列直接忽略，而不是将整个表达式赋值为null。

可做如下处理：

SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT custid, country, region, city,
  country + N',' + region + N',' + city AS location
FROM Sales.Customers;

执行结果：

custid      country         region city            location
----------- --------------- ------ --------------- -------------------
1           Germany         NULL   Berlin          Germany,,Berlin
2           Mexico          NULL   México D.F.     Mexico,,México D.F.
3           Mexico          NULL   México D.F.     Mexico,,México D.F.
4           UK              NULL   London          UK,,London
5           Sweden          NULL   Luleå           Sweden,,Luleå
6           Germany         NULL   Mannheim        Germany,,Mannheim
7           France          NULL   Strasbourg      France,,Strasbourg
8           Spain           NULL   Madrid          Spain,,Madrid
9           France          NULL   Marseille       France,,Marseille
10          Canada          BC     Tsawassen       Canada,BC,Tsawassen
11          UK              NULL   London          UK,,London
12          Argentina       NULL   Buenos Aires    Argentina,,Buenos Aires
13          Mexico          NULL   México D.F.     Mexico,,México D.F.
14          Switzerland     NULL   Bern            Switzerland,,Bern
15          Brazil          SP     Sao Paulo       Brazil,SP,Sao Paulo
16          UK              NULL   London          UK,,London
17          Germany         NULL   Aachen          Germany,,Aachen
18          France          NULL   Nantes          France,,Nantes
19          UK              NULL   London          UK,,London
20          Austria         NULL   Graz            Austria,,Graz
...

(91 row(s) affected)