BigQuery - Converting string to numeric

Question

I am looking for a regular expression syntax accepted by RE2, to convert the following strings to numeric:

"339,840" -> 339840

"$100,000" -> 100000

"0.75" -> 0.75

"1" -> 1

Answer 1

Below is for BigQuery Standard SQL

You can use cast(regexp_replace(val, r'[^\d.]', '') as numeric)

See below example

#standardSQL
with `project.dataset.table` as (
  select "339,840" val union all
  select "$100,000" union all
  select "0.75" union all
  select "1" 
)
select cast(regexp_replace(val, r'[^\d.]', '') as numeric)
from `project.dataset.table`

with output