MSSQL Server处理Null语句
Null，在T-SQL中是个很诡异的内容，它的处理方式不按常规来走，以至于我们在处理数据的时候，经常会变的手足无措，这里讨论下，null与字符类型拼接的情况。
以下语句：
[sql]
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)