title: How to deal with a column that has varchar values as well as large numbers that show up as scientific format?
body: <p>I'm loading data from csv files into tables in AWS Athena. There is a string column that has varchar values such <code>ABC123123</code> and really large numbers like <code>2588000000</code> (shows up in scientific format 2.588E+10) on parsing the csv and I want to convert only the scientific format to float so it shows up at <code>2588000000</code> and not with the scientific format.
  I tried the following:</p>
  <p><code>SELECT ..., cast(column2 as float) FROM &quot;db&quot;.&quot;table&quot;</code></p>
  <p>But I obviously cant CAST as float since there are varchar values in the same column.</p>
  <p>I also tried the following:</p>
  <pre><code>SELECT column1, 
  (CASE 
          WHEN column2 like '%E-%' THEN CAST(column2 AS FLOAT))
          WHEN column2 like '%E+%' THEN CAST(column2 AS FLOAT))
          ELSE column2
  END)
  
  FROM &quot;db&quot;.&quot;table&quot; 
  </code></pre>
  <p>And Athena gave this error <a href="https://i.stack.imgur.com/bz3X5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bz3X5.png" alt="enter image description here" /></a></p>
  <p>Any help is appreciated!</p>