CODEWITHSUNDEEP
regexgoogle-bigquery
idan
idan

Reputation: 2200

Big query REGEXP_REPLACE text to integer

How to extract integer value from the following format text For example, this text ($99.99) should return -99. and 99.99 should return 99 and 80 should return 80

I tried use [REGEXP_REPLACE][1](text, r'[^\d]+',"") but I'm not fimiliar with regex, I red this documentation but not understand how to use.

Upvotes: 1

Views: 811

Answers (2)

Ryszard Czech
Ryszard Czech

Reputation: 18641

Use

CAST(REGEXP_REPLACE(REGEXP_REPLACE(text, r'\(\$(\d+)\.\d+\)', r'-\1'), r'\.\d+$', '') as int64) int

See regex #1 proof and regex proof #2.

\(\$(\d+)\.\d+\) explanation

--------------------------------------------------------------------------------
  \(                       '('
--------------------------------------------------------------------------------
  \$                       '$'
--------------------------------------------------------------------------------
  (                        group and capture to \1:
--------------------------------------------------------------------------------
    \d+                      digits (0-9) (1 or more times (matching
                             the most amount possible))
--------------------------------------------------------------------------------
  )                        end of \1
--------------------------------------------------------------------------------
  \.                       '.'
--------------------------------------------------------------------------------
  \d+                      digits (0-9) (1 or more times (matching
                           the most amount possible))
--------------------------------------------------------------------------------
  \)                       ')'

\.\d+$ explanation

--------------------------------------------------------------------------------
  \.                       '.'
--------------------------------------------------------------------------------
  \d+                      digits (0-9) (1 or more times (matching
                           the most amount possible))
--------------------------------------------------------------------------------
  $                        before an optional \n, and the end of the
                           string

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173161

Try below (BigQuery Standard SQL)

cast(regexp_replace(translate(text, '($)', '-'), r'\.\d*', '') as int64) int

if to apply to sample data from your question - output is

enter image description here

Upvotes: 2

Related Questions