Convert String ($1,234.50) to Number field

[tquinn-16436437]

I have a string field that contains a dollar figure of total cost. It is a string field but I need it to be numeric so that I can use aggregate functions. I am having trouble converting the field from string to numeric. So far I am able to format the field to remove the “$” and the “,” but I have not been successful in ending up with a straight numeric field. I have included the errors I received in big query. 

Attempt 1: 

cast(replace(replace(total_costs,"$",""),",","") as INT)

error: Bad int64 value: 39500.00

Attempt 2:

cast(replace(replace(total_costs,"$",""),",","") as decimal)

error: Invalid NUMERIC value:

Can anyone please help?

[olga1]

Hiya!

Check this example here: 

• https://community.looker.com/lookml-5/best-way-to-cast-from-string-to-number-bigquery-13676

You can regexp_replace to remove not needed symbols and cast then 

[tquinn-16436437]

I tried the regex_replace function as well but my problem is that the cast isn’t working on my field containing decimal values. 

cast(regexp_replace(total_costs, r"\$|,|","") as numeric)

error = Invalid NUMERIC value: