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)