CODEWITHSUNDEEP
regexgoogle-bigqueryre2
ronencozen
ronencozen

Reputation: 2231

BigQuery - Converting string to numeric

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

Upvotes: 0

Views: 981

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173151

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

enter image description here

Upvotes: 1

Related Questions