CODEWITHSUNDEEP
regexgoogle-bigquery
S.Nori
S.Nori

Reputation: 113

GCP Bigquery REGEX_REPLACE

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.

Upvotes: 0

Views: 731

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

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

Upvotes: 1

Related Questions