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

Question

For reasons I can not help I have a varchar column with data like the following: 820.0E-12, 10.0E+00.

I want the numeric value. So I have this test query which works:

declare @d varchar(256)
set @d = '820.0E-12'
select 
   CASE 
      WHEN @d like '%E-%' THEN LTRIM(RTRIM(CAST(CAST(@d AS FLOAT) AS DECIMAL(18,18))))
      WHEN @d like '%E+%' THEN NULL
      ELSE @d
   END

My result is: 0.000000000820000000 (which is what I want)

I change my SQL to account for the numbers > 0 (10.0E+00) like this:

WHEN @d like '%E+%' THEN CAST(@d AS FLOAT)

My result changes to: 8.2E-10 (which is NOT what I want)

If I change @d='10.0E+00' then I get 10 (which is correct).

I've got a view that I need to make the output from a varchar column, that contains scientific notation, casted/converted into decimal(18,18).

Can somebody tell me what craziness is going on here?

Or, maybe my question should be, how do I cast/convert a varchar scientific notation column to decimal output in a view?

My first WHEN statement works for numbers < 0 but I also need to account for numbers > 0. When I change the second WHEN, to include the CAST, it breaks/gives the wrong result.

Answer 1

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))