时间戳

首页 > 解决方案 > 将包含以科学计数法表示的数字的 varchar 转换为十进制时出错

sql-server - 将包含以科学计数法表示的数字的 varchar 转换为十进制时出错

问题描述

我目前正在从事一个 ETL 项目，其中来自 mongoDB 的数据被转换为关系数据库并存储在 SQL Server 中。我遇到的问题是 mongoDB 中同一字段中的一些数字存储为十进制（0.058823），有时使用科学计数法（5.8823e-02）。

当我使用 Postgres 时，任何一种格式的值都作为双精度值传递，我在查询任何一种格式时都没有问题。SQL Server 似乎并非如此。

目前，所有数据都作为varchar和在同一个表的视图中传递，我使用以下代码：

CAST (CASE 
         WHEN [fieldname] LIKE '%e%' 
            THEN log([fieldname]) 
            ELSE [fieldname] 
      END AS DECIMAL(30, 20)) AS [FieldName1]

在一个字段中还有一个列表，我将其转换为需要使用 CTE 的子字符串和强制转换。这将我的代码从 100 多行变成了近 600 多行。想知道是否有更简单的方法？

我非常感谢您能提供的任何帮助。

标签： sql-servertsql

解决方案

SQL Server 支持科学记数法和“常规”小数。

这是一个简单的例子：

DECLARE @D decimal(10, 6) = 0.058823,
        @S decimal(10, 6) = 5.8823e-02

SELECT  @D As Regular, 
        @S As Scientific, 
        IIF(@D = @S, 1, 0) As AreEqual

此 select 语句的结果是：

Regular     Scientific  AreEqual
0.058823    0.058823    1

但是，将 fromvarchar转换为 decimal 与常规小数完美配合，但会引发科学记数法错误：

DECLARE @SD varchar(10) = '0.058823',
        @SS varchar(10) = '5.8823e-02'                 

SELECT CAST(@SD AS decimal(10, 6)) As RegularString,
       CAST(@SS AS decimal(10, 6)) As ScientificString

引发此错误：

将数据类型 varchar 转换为数字时出错。

另一方面，转换为浮点数非常有效 - 所以要获得小数，您可以转换为浮点数，然后转换为十进制数：

SELECT  CAST(@SD AS decimal(10, 6)) As RegularString,
        CAST(CAST(@SS AS float) AS decimal(10, 6)) As ScientificString

结果：

RegularString   ScientificString
0,058823        0,058823

推荐阅读