Convert String ($1,234.50) to Number field

Looker Q&A Looker
,
1

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?

2

Hiya!

Check this example here:

You can regexp_replace to remove not needed symbols and cast then

3

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:

1 Like