title: Casting Scientific Notation (from varchar -> numeric) in a view
body: <p>For reasons I can not help I have a varchar column with data like the following: 820.0E-12, 10.0E+00.</p>
  
  <p>I want the numeric value. So I have this <strong><em>test</em></strong> query which works:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>My result is: <strong>0.000000000820000000</strong> (which is what I want)</p>
  
  <p>I change my SQL to account for the numbers > 0 (10.0E+00) like this:</p>
  
  <pre><code>WHEN @d like '%E+%' THEN CAST(@d AS FLOAT)
  </code></pre>
  
  <p>My result changes to: <strong>8.2E-10</strong> (which is NOT what I want)</p>
  
  <p>If I change <strong>@d='10.0E+00'</strong> then I get <strong>10</strong> (which is correct).</p>
  
  <p>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).</p>
  
  <p><strong>Can somebody tell me what craziness is going on here?</strong></p>
  
  <p><strong>Or, maybe my question should be, how do I cast/convert a varchar scientific notation column to decimal output in a view?</strong></p>
  
  <p>My first WHEN statement works for numbers &lt; 0 but I also need to account for numbers > 0. When I change the second WHEN, to include the <strong>CAST</strong>, it breaks/gives the wrong result.</p>