GCP Bigquery REGEX_REPLACE

Question

The amount field in the ingested table is a string. Typical values will be like: £1,000.00

I would like to convert that into float. I'm using the following in the BigQuery:

SELECT SAFE_CAST(REGEXP_REPLACE('£1,000',r'(£)','') AS FLOAT64)

Then the output is: NULL

The reason for this is:

SELECT REGEXP_REPLACE('£1,000',r'(£)','')

The output is: 1,000

Because of the comma (,) in the output, it is not able to convert the value into a Float in the SAFE_CAST.

How do I remove both '£' and ',' from the string '£1,000' using REGEXP_REPLACE?

Thanks a lot.

Mikhail Berlyant · Accepted Answer · 2018-12-18 23:15:25Z

1

SELECT SAFE_CAST(REGEXP_REPLACE('£1,000', r'[£,]', '') AS FLOAT64)

answered Dec 18, 2018 at 23:15

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

great! consider also voting up (if not yet) :o)