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?
Hiya!
Check this example here:
You can regexp_replace to remove not needed symbols and cast then
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: