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.89SELECT 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.89SELECT 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.6SELECT @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.89SELECT FORMAT(@FL,'######.###') AS FLOAT_BY_FORMAT,
FORMAT(@RL,'######.###') AS REAL_BY_FORMAT

结果

FLOAT_BY_FORMAT REAL_BY_FORMAT
1234567.89 1234568

SQL Server ->> 利用CONVERT/STR/FORMAT函数把浮点型数据格式化/转换成字符串的更多相关文章

SQL Server利用RowNumber()内置函数与Over关键字实现通用分页存储过程（支持单表或多表结查集分页）
SQL Server利用RowNumber()内置函数与Over关键字实现通用分页存储过程,支持单表或多表结查集分页,存储过程如下: /******************/ --Author:梦在旅 ...
SQL Server 利用WITH AS递归获取层级关系数据
WITH AS短语,也叫做子查询部分(subquery factoring),在SQL Server 2005中提供了一种解决方案,这就是公用表表达式(CTE),使用CTE,可以使SQL语句的可维护性 ...
在Sql Server 2005中将主子表关系的XML文档转换成主子表“Join”形式的表
本文转载:http://www.cnblogs.com/Ricky81317/archive/2010/01/06/1640434.html 最近这段时间在Sql Server 2005下做了很多根据 ...
1 sql server 利用多重赋值将一列的数据以逗号分隔，返回
declare @mav varchar(max) select @mav=coalesce(@mav+', '+d.Name,d.Name) from ( select Name from Huma ...
SQL Server 2008 R2 常用系统函数学习
/******************************************* * 聚合函数 *******************************************/ SEL ...
（转载）MS SQL Server 未公开的加密函数有哪些？
MS SQL Server 未公开的加密函数有哪些? 以下的文章是对MS SQL Server 未公开的加密函数的具体操作,如果你对其相关的实际操作有兴趣的话,你就可以点击了. MS SQL Serv ...
SQL SERVER中用户定义标量函数（scalar user defined function）的性能问题
用户定义函数(UDF)分类 SQL SERVER中的用户定义函数(User Defined Functions 简称UDF)分为标量函数(Scalar-Valued Function)和表值函数(T ...
SQL Server使用convert对datetime日期数据进行获取
来源:http://database.51cto.com/art/201007/211883.htm 备注:本文的语法讲解确实是比较乱,似乎格式不太严谨.参考时还是以实例验证为准比较好以下的文章主要 ...
SQL Server如何定位自定义标量函数被那个SQL调用次数最多浅析
前阵子遇到一个很是棘手的问题,监控系统DPA发现某个自定义标量函数被调用的次数非常高,高到一个离谱的程度.然后在Troubleshooting这个问题的时候,确实遇到了一些问题让我很是纠结,下文是解决 ...

随机推荐

oracle12c之二控制PDB中SGA 与 PGA 内存使用
oracle12c之控制pdb中sga 与 pga 内存使用 Memory Management using Resource Manager Oracle数据库资源管理器(资源管理器)现在可以在多 ...
Protrator 官网和下载工具稍慢 , 但能使用. Angular CLI 内置官方推荐 TS的 demo 不明显 , 而且依赖无法安装
npm uninstall -g protractor && cnpm install -g protractor && protractor --version 复 ...
JavaScript 刷题一
最近读<JavaScirpt编程精解>,想把里面的三个大的程序实现,现在记录下来. 问题一: 从下面这封信中,emily奶奶每封信的结尾都会用同样的格式注明哪只猫出生了,哪只猫死去了.现要 ...
ORACLE迁移GP实践
最近在做oracle到greenplum的迁移实践,步骤如下: 1. 使用ora2pg实现Oracle的数据结构迁移到GP的实现过程 2. Oracle的数据迁移到GP的实现过程 1. ora2p ...
orcale 之 SQL 数据查询
从数据库中检索行,并允许从一个或多个表中选择一个或多个行或列.虽然 SELECT 语句的完整语法较复杂,但是其主要的子句可归纳如下: SELECT select_list [ INTO new_tab ...
用.net 2.0(或.net 3.5)开发的程序在.net 4.0的环境中运行的解决方案
引用:.NET2.0程序集无法在.net 4.0 中运行的解决方案 1. 原来是在.net 2.0 (VS2008)的环境下的程序,通过升级到.net 4.0 (VS2013)导致程序运行不了提示需 ...
html中使用滚动条
1. 在html页面中使用滚动条,效果如下: 代码如下: <div style="height:auto !important;max-height:200px;overflow:sc ...
Mybatis基本介绍
Mybatis介绍 1.Mybatis介绍 MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了goog ...
jqgrid 加载时第一页面只显示多少条数据
function initGrid() { localGrid = jQuery("#tbList"); localGrid.jqGrid({ data: localData, d ...
第八章使用java实现面向对象-File I/O
java.io.File类用于表示文件(目录) File类只用于表示文件(目录)的信息(名称.大小等),不能用于文件内容的访问 RandomAccessFile java提供的对文件内容的访问,既可以 ...