Casting Scientific Notation (From Varchar -> Numeric) in a View

Casting Scientific Notation (from varchar - numeric) in a view

There's a couple different problems all coming together here at the same time. Let's look at some of them:

1. You're casting numbers as DECIMAL(18, 18). What that means is "give me a number that has room for a TOTAL of 18 characters, and 18 of them should be after the decimal". That works fine as long as your number is smaller than 0 (which is true for all E- numbers) but it will break if you try to use it on numbers > 0. For numbers > 0, just cast as DECIMAL without specifying anything else.

2. In the case where you add "WHEN @d like '%E+%' THEN CAST(@d AS FLOAT)", you're getting different results for numbers < 0 because the engine is implicitly casting the result differently. I don't know the rules on how sql server decides to cast CASE results, but apparently making your proposed change causes the engine to recast it in a different way. Explicitly casting those results as decimal fixes the issue.

3. You need to LTRIM and RTRIM your results consistently. You can either add LTRIM and RTRIM to each case statement, or you can just LTRIM and RTRIM the results of the case.

Here's a solution that should totally solve everything:

SELECT
    LTRIM(RTRIM(CASE 
        WHEN @d like '%E-%' THEN CAST(CAST(@d AS FLOAT) AS DECIMAL(18,18))
        WHEN @d like '%E+%' THEN CAST(CAST(@d AS FLOAT) AS DECIMAL)
        ELSE @d
    END))

Error casting varchar containing a number expressed in scientific notation to decimal

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

Omitting Scientific Notation in Numeric columns when working with Dynamic SQL returning JSON

It's too long for a comment, so I post this as an answer. I'm able to find only these explanations in the documentation about how FOR JSON converts SQL Server data types to JSON types. So, as a possible workaround, you may try to convert the float columns to numeric using information from system catalog views (I assume, that the SELECT is against a view).

DECLARE @cols varchar(MAX) = STUFF(
    (
    SELECT 
        ', ' +
        CASE 
           WHEN t.[name] = 'float' THEN 'CONVERT(numeric(10, 2), [' + p.[description] + ']) AS [' + p.[description] + N'] '
           ELSE p.[description] 
        END
    FROM sys.columns c
    JOIN sys.views v ON c.object_id = v.object_id
    JOIN sys.schemas s ON v.schema_id = s.schema_id
    JOIN sys.types t ON c.system_type_id = t.system_type_id
    JOIN @PermittedColumnIDs p ON p.[description] = c.[name]
    WHERE v.[name] = (SELECT ViewName FROM @DynamicQueryProps) AND s.[name] = 'dbo'
    FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)'), 1, 1, ''
)

DECLARE @query nvarchar(max) 
SET @query = 
   N' SELECT TOP 1000 '+ @Cols +
   N' FROM [' + (SELECT ViewName FROM @DynamicQueryProps) + ']' +
   N' FOR JSON AUTO';

EXECUTE sp_executesql @query

Convert scientific notation string to number in Hive

How about simple cast ?

cast (9.12e7 as  BIGINT)
OR
cast ('9.12E7' as  decimal(8,0))

Pls check which one is working for you.

Screenshot below.

SQL - return all float values in field without writing in scientific notation

Jeroen's comment was helpful, and gave me the expression to give me the desired results:

select FORMAT([sampleField], '0.' + REPLICATE('#', 308))

Where I think I just have to bring down the '308' to a number that represents the highest level of precision seen in our processes. Thank you Jeroen for that solution!

---

Related Topics