問題描述

我目前正在從事一個 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 轉換為十進制時出錯的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！