[mssql/sql server]sql server中字符串数字转换函数的替换函数等
数字转换为字符串本来用 STR 函数即可，但有些附作用，所以 2012 出了 format 函数。没有这个版本的话可以加 REPLACE 函数代替。
例如
SELECT TOP 100
'1234567890' + REPLACE(str([PATIENT_ID], 8), ' ', '0') --加8位数，不够8位前面加字符 0
参考 https://www.cnblogs.com/jenrrychen/p/5442587.html
--------------------------------------------------------
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
分类: SQL Server
