在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 1234567.89 1234567.89 (1 row(s) affected)
那麼有什麼辦法可以避免上述情況呢？
一共有3種辦法可以在保留原始數據樣子的情況下成功轉換浮點型數據成為字符串
1）使用STR函數
作為方法之一，它並不是三種方法中最好，原因是STR函數會產生空格補全預定總長度和以0補全預定小數點長度。如果你希望真真實實的原始數據就需要你利用LTRIM清除頭部空格和SUBSTRING+PATINDEX清除尾部的0數字串。需要額外的一些工作。
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 STR(@FL,24,8) AS PURE_BY_STR,
REVERSE(SUBSTRING(REVERSE(LTRIM(STR(@FL,24,8))),PATINDEX('%[1-9]%',REVERSE(LTRIM(STR(@FL,24,8)))),LEN(REVERSE(LTRIM(STR(@FL,24,8))))-PATINDEX('%[1-9]%',REVERSE(LTRIM(STR(@FL,24,8))))+1)) AS FLOAT_BY_STR,
REVERSE(SUBSTRING(REVERSE(LTRIM(STR(@RL,24,8))),PATINDEX('%[1-9]%',REVERSE(LTRIM(STR(@RL,24,8)))),LEN(REVERSE(LTRIM(STR(@RL,24,8))))-PATINDEX('%[1-9]%',REVERSE(LTRIM(STR(@RL,24,8))))+1)) AS REAL_BY_STR
結果
PURE_BY_STR FLOAT_BY_STR REAL_BY_STR
------------------------ ------------------------ ------------------------
1234567.89000000 1234567.89 1234567.875
(1 row(s) affected)
2）使用CONVERT/CAST函數先將數據轉換成DECIMAL精確值
這種其實和STR有些相似的地方，就是都是把浮點型數據轉換成精確值先。它唯一比STR好的地方就是沒有前面空格補全的煩惱。可是還是避免不了精確值的以0補全尾部的情況。所以其實它沒比STR好到哪去。
DECLARE @FL AS FLOAT
DECLARE @RL AS REAL
DECLARE @DC AS DECIMAL(18,2)
SET @FL = 12345.6
SET @RL = 12345.6
SET @DC = 12345.6
SELECT @FL AS FLOAT_VALUE,
@RL AS REAL_VALUE,
CONVERT(NVARCHAR, CAST(@FL AS DECIMAL(24,8))) AS PURE_FLOAT_BY_STR,
CONVERT(NVARCHAR, CAST(@RL AS DECIMAL(24,8))) AS PURE_REAL_BY_STR,
REVERSE(SUBSTRING(REVERSE(CONVERT(NVARCHAR, CAST(@FL AS DECIMAL(24,8)))),PATINDEX('%[1-9]%',REVERSE(CONVERT(NVARCHAR, CAST(@FL AS DECIMAL(24,8))))),LEN(REVERSE(CONVERT(NVARCHAR, CAST(@FL AS DECIMAL(24,8)))))-PATINDEX('%[1-9]%',REVERSE(CONVERT(NVARCHAR, CAST(@FL AS DECIMAL(24,8)))))+1)) AS FLOAT_BY_CONVT,
REVERSE(SUBSTRING(REVERSE(CONVERT(NVARCHAR, CAST(@RL AS DECIMAL(24,8)))),PATINDEX('%[1-9]%',REVERSE(CONVERT(NVARCHAR, CAST(@RL AS DECIMAL(24,8))))),LEN(REVERSE(CONVERT(NVARCHAR, CAST(@RL AS DECIMAL(24,8)))))-PATINDEX('%[1-9]%',REVERSE(CONVERT(NVARCHAR, CAST(@RL AS DECIMAL(24,8)))))+1)) AS REAL_BY_CONVT
結果
FLOAT_VALUE REAL_VALUE PURE_FLOAT_BY_STR PURE_REAL_BY_STR FLOAT_BY_CONVT REAL_BY_CONVT ---------------------- ------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ 12345.6 12345.6 12345.60000000 12345.59960938 12345.6 12345.59960938 (1 row(s) affected)
既然這裡用到了REAL，那就順便說一下FLOAT和REAL這兩種數據類型。
從上面的結果可以看到REAL數據在轉換過程中已經發生了數據值的變化。由於它自身精度小，相當於FLOAT(24)，而默認的FLOAT類型是FLOAT(53)，所以FLOAT在數據轉換過程中可以避免上面REAL出現的數據發生改變的情況。我建議還是盡量避免使用REAL。
3）使用SQL Server 2012新增加的FORMAT函數
這個是三者我認為最好的選擇。四個字：簡單利索。
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 FORMAT(@FL,'######.###') AS FLOAT_BY_FORMAT,
FORMAT(@RL,'######.###') AS REAL_BY_FORMAT
結果
FLOAT_BY_FORMAT REAL_BY_FORMAT 1234567.89 1234568
