CODEWITHSUNDEEP
sqlamazon-web-servicesprestoamazon-athenascientific-notation
bourgeoisna
bourgeoisna

Reputation: 115

How to deal with a column that has varchar values as well as large numbers that show up as scientific format?

I'm loading data from csv files into tables in AWS Athena. There is a string column that has varchar values such ABC123123 and really large numbers like 2588000000 (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 2588000000 and not with the scientific format. I tried the following:

SELECT ..., cast(column2 as float) FROM "db"."table"

But I obviously cant CAST as float since there are varchar values in the same column.

I also tried the following:

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 "db"."table"

And Athena gave this error enter image description here

Any help is appreciated!

Upvotes: 2

Views: 2134

Answers (2)

Piotr Findeisen
Piotr Findeisen

Reputation: 20770

Try try:

SELECT coalesce(try(cast(cast(col AS doub) AS varchar)), col)
FROM ...

However, at least in current Presto, the double numbers end up cast to varchar with scientific notation, so you may need to go through decimal:

SELECT
  coalesce(
    try(
      cast(cast(cast(col AS double) AS decimal(38,19)) AS varchar)),
      col)
FROM ...

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270653

Hmmmm . . . You need for the case expression to return a string. So, format the float value as a string:

SELECT column1, 
       (CASE WHEN REGEXP_LIKE(column2, '^[0-9]+E[+-]?[0-9]+$')
             THEN FORMAT('%f', CAST(column2 AS FLOAT))
             ELSE column2
        END)
FROM "db"."table"

Upvotes: 0

Related Questions