SQL Server ->> 利用CONVERT/STR/FORMAT函数把浮点型数据格式化/转换成字符串

在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