将包含以科学记数法表示的数字的 varchar 转换为十进制时出错 [英] Error casting varchar containing a number expressed in scientific notation to decimal

问题描述

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

I am currently working on a ETL project where data from mongoDB is getting converted to relational database and stored in SQL Server. The problem I am having is that some of the numbers in the same field in mongoDB is stored as decimal (0.058823) and sometimes using scientific notation (5.8823e-02).

当我使用 Postgres 时，任何一种格式的值都作为双精度值传递，而且我在查询任何一种格式时都没有问题.SQL Server 似乎不是这种情况.

When I was working with Postgres the values in either format got passed as double values and I had no issues querying with either format. This does not seem to be the case with SQL Server.

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

Currently all the data is passed as varchar and in the view of the same table I am using the following code:

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

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

There are also list in a field that I am turning into substring and casting which requires using CTE. This is turning my code from 100+ to almost 600+ lines. Was wondering if there is a simpler way?

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

I really appreciate any help you can provide.

推荐答案

SQL Server 支持科学记数法和常规"小数.

SQL Server supports both scientific notation and "regular" decimals.

这是一个简单的例子:

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语句的结果是:

The result of this select statement is:

Regular     Scientific  AreEqual
0.058823    0.058823    1

然而，从 varchar 转换为十进制与常规小数完美配合，但会引发科学记数法错误:

However, casting from varchar to decimal works perfectly with regular decimals, but raises an error with scientific notation:

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 转换为数字时出错.

Error converting data type varchar to numeric.

另一方面，转换为浮点数效果很好 - 所以要获得小数，您可以先转换为浮点数，然后再转换为小数:

Casting to float, on the other hand, works perfectly - so to get a decimal you can cast to float and then to decimal:

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

这篇关于将包含以科学记数法表示的数字的 varchar 转换为十进制时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！