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

Mikhail Berlyant · Accepted Answer · 2020-11-10 19:56:12Z

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

answered Nov 10, 2020 at 19:56

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

1 Comment

ronencozen Over a year ago

Very elegant, Thank you!

Collectives™ on Stack Overflow

BigQuery - Converting string to numeric

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related