Amazon Athena- Querying columns with numbers stored as string

Question

I have a insurance dataset which includes the number of enrollment for each county. However the number of enrollments is stored as a string. How can i query the data for something like "Find the plans which have a enrollment of more than 50". Unfortunately 50 is stored as a string in the dataset so i need to understand how to run my query using athena. Can someone help please

Solution

Cast string to floating point number, not integer, and remove commas before cast. Here is an example:

with x AS 
    (SELECT '1,800,850.20' AS "value")
SELECT cast(replace(value,',', '') AS REAL)
FROM x

Therefore, you should use:

SELECT
  npi,
  CAST(REPLACE(total_submitted_charge_amount,',', '') AS REAL) AS charge_amount
FROM cmsaggregatepayment2017
WHERE CAST(REPLACE(total_submitted_charge_amount,',', '') > 100000
ORDER BY CAST(REPLACE(total_submitted_charge_amount,',', '') ASC
LIMIT 1000