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).
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.
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]
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 supports both scientific notation and "regular" decimals.
Here's a simple example:
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
The result of this select statement is:
Regular Scientific AreEqual
0.058823 0.058823 1
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
raise this error:
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
Results:
RegularString ScientificString
0,058823 0,058823
Since your main concern seems to be the code bloat (600 lines vs. 100), you can create a function
CREATE FUNCTION dbo.GetDecimal(@input VARCHAR(32))
RETURNS DECIMAL(30,20)
AS
BEGIN
RETURN CAST(CAST(@input AS FLOAT) AS DECIMAL (30, 20))
END
and call it in your SELECT statement for each field to be converted, like this:
SELECT
dbo.GetDecimal(sfield1) as tfield1,
dbo.GetDecimal(sfield2) as tfield2,
...
FROM ...
Note: the first comment on your question correctly pointed out that you should not be using the log() function. Instead, this user function does an intermediate cast to float before the final cast to decimal. This is necessary because string values expressed in scientific notation cannot be cast directly to decimal.
