CODEWITHSUNDEEP
sqlgoogle-bigquery
ebuzz168
ebuzz168

Reputation: 1194

Thousands separator in sql with dot in bigquery

How to cast numeric type let say 30000 into string type with dot as thousand separator 30.000.

I tried to use

SELECT FORMAT("%'.0f", CAST(30000 as NUMERIC))

But the result is 30,000 not 30.000

Upvotes: 0

Views: 4456

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173036

Most likely in some case you will need not just replace , with . but also replace . to , to format numbers with decimals
In such cases consider below example

SELECT 
  TRANSLATE(
    FORMAT("%'.2f", CAST(30000.12 as NUMERIC)),
    ',.',
    '.,'
  )

with output

30.000,12

Upvotes: 5

Evert
Evert

Reputation: 99618

You could just replace the , with . after formatting:

SELECT 
  REPLACE(
    FORMAT("%'.0f", CAST(30000 as NUMERIC)),
    ',',
    '.'
  )

Upvotes: 2

Related Questions