CODEWITHSUNDEEP
sqlgoogle-bigquery
Simon Breton
Simon Breton

Reputation: 2876

Convert string number with format X XXX,XX to number

How do I convert this string 3 849,59 into a readable number format?

The result would be 3849,59

I've tried REGEXP_REPLACE(x,' ','') which is working with others string but doesn't seem to work with "number" string...

I'm wondering if is there something like date_format for numbers.

Thanks.

Upvotes: 0

Views: 2202

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Looks like you have spaces where commas expected and commas where dot expected

So try below 

SELECT CAST(REPLACE(REPLACE(x, ' ',''), ',', '.') AS FLOAT64)

for example 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '3 849,59' x
)
SELECT CAST(REPLACE(REPLACE(x, ' ',''), ',', '.') AS FLOAT64)
FROM `project.dataset.table`

returns 

Row f0_  
1   3849.59

Upvotes: 1

Related Questions