Why can't a cast a number into an int?

Question

I've got a dataset which I'm trying to convert a column to an int. There are some blank values (so not a null) but the error I got is:
INVALID_CAST_ARGUMENT: Cannot cast '1,00' to INT

Do I have to remove the comma before?  The query I'm using is:

, (CASE hoeveelheid WHEN '' THEN null ELSE CAST(hoeveelheid as int) END) hoeveelheid

Accepted Answer

I see the problem.  The CAST includes the "as int".  Try this:

`, (CASE hoeveelheid WHEN '' THEN null else (CAST(Replace(hoeveelheid, ',00', '') as int)) END) hoeveelheid`

Answer

This will probably help you more than my previous answer.  Just saw the athena tag.

https://stackoverflow.com/questions/59080059/amazon-athena-querying-columns-with-numbers-stored-as-string

Answer

Thank you for your answers. I think I'm close but I still get erros. Now I'm using this:

, (CASE hoeveelheid WHEN '' THEN null else (CAST(Replace(hoeveelheid, ',00', '')) as int) END) hoeveelheid

The error: line 7:81: mismatched input ')'. Expecting:

Answer

You don't mention which AWS service you are using, but if you're using redshift, did you see this document on TO_NUMBER?

https://docs.aws.amazon.com/redshift/latest/dg/r_TO_NUMBER.html

Why can't a cast a number into an int?

Relevant content