SQLServer之CAST

近日在处理一个客户反馈的问题时，走查代码，发现对异常的保护很不够。

比如假设有这么一张表，表名为Data，字段都为int类型，允许为NULL。

no	A1	A2	A3	A4	A5
1	-80	-81	-82	NULL	NULL
2	-90	-91	NULL	-92	NULL

现在需要把A1-A5，转换成一个字符串，数值间用分号连接。对于NULL的数据，用空字符串代替。对于上面的表数据，结果是如下两个字符串：

-80;-81;-82;;;

-90;-91;;-92;;

原来的写法是：

 SELECT CAST(Data.A1 AS nvarchar(20)) + ';' + CAST(Data.A2 AS nvarchar(20))
 + CAST(Data.A3 AS nvarchar(20))+ ';' + CAST(Data.A4 AS nvarchar(20))
 + ';' + CAST(Data.A5 AS nvarchar(20)) AS [Ax] 
 FROM Data

上面的语句，得到的结果是NULL。

根据SQLServer的CAST定义：

-- Syntax for CAST:  
CAST ( expression AS data_type [ ( length ) ] )

对于expression为NULL时，显然NULL并不能转变成任何的data_type，返回结果为NULL。

而对于X+NULL这类表达式来说，其结果仍然为NULL。究其原因，是因为SQL Server是三值逻辑（TRUE，FALSE，UNKNOWN）。UNKNOWN就是用以处理NULL的。

因此，上面的SQL语句，输出NULL是可以预期的结果。

那么如何修改呢？有人提出用ISNULL函数可否？ISNULL定义如下：

ISNULL ( check_expression , replacement_value )

其中， check_expression为 将被检查是否为 NULL 的 表达式 。 check_expression  可以为任何类型。

replacement_value为当  check_expression 为 NULL 时要返回的表达式。 而根据msdn，特别需要注意的是：replacement_value 必须是可以隐式转换为  check_expresssion 类型的类型。 也就是说，ISNULL(Data.A3, '')，返回的将是0，而不是空字符串。因为A3的类型是int。 因此ISNULL也是行不通的。
上述需求的最终解决办法，是对每个字段进行is null判断，即类似：

is null ? '': CAST （expression AS data_type）

翻译成SQL语句为下：

 
 SELECT CASE when Data.A1 is null then '' else CAST(Data.A1 AS nvarchar(20)) END + ';' +
        CASE when Data.A2 is null then '' else CAST(Data.A2 AS nvarchar(20)) END + ';' +
	CASE when Data.A3 is null then '' else CAST(Data.A3 AS nvarchar(20)) END + ';' +
	CASE when Data.A4 is null then '' else CAST(Data.A4 AS nvarchar(20)) END + ';' +
	CASE when Data.A5 is null then '' else CAST(Data.A5 AS nvarchar(20)) END  AS [Ax]
 from Data

对于有趣的NULL，可以参考如下链接：http://www.cnblogs.com/changbluesky/archive/2010/05/07/1729519.html