在SQL Server下想把数字(包括浮点型和整型)转换成字符串,保留数据原本的样子或者根据需要转换成另外指定的格式可能就不仅仅是一条CAST(XXXX AS NVARCHAR)这么简单的事情了。
无论是CAST或者CONVERT在转换FLOAT或者REAL类型成为字符串的时候都可能面临一个问题,就是最终的数据会编程科学记数法的形式出现在最终结果集中。而DECIMAL是没有遇到这个问题。
比如:
DECLARE @FL AS FLOAT DECLARE @RL AS REAL DECLARE @DC AS DECIMAL(18,2) SET @FL = 1234567.89 SET @RL = 1234567.89 SET @DC = 1234567.89 SELECT CAST(@FL AS NVARCHAR) AS FLOAT_BY_CAST, CONVERT(NVARCHAR, @FL) AS FLOAT_BY_CONVERT, CAST(@RL AS NVARCHAR) AS REAL_BY_CAST, CONVERT(NVARCHAR, @RL) AS REAL_BY_CONVERT, CAST(@DC AS NVARCHAR) AS DECIMAL_BY_CAST, CONVERT(NVARCHAR, @DC) AS DECIMAL_BY_CONVERT
结果
FLOAT_BY_CAST FLOAT_BY_CONVERT REAL_BY_CAST REAL_BY_CONVERT DECIMAL_BY_CAST DECIMAL_BY_CONVERT ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ 1.23457e+006 1.23457e+006 1.23457e+006 1.23457e+006