用点将逗号更改为次单位值 [英] Change comma with dot for sub unitary values

问题描述

我有一列用逗号分隔的值，例如 1,6 和 8 .我在BigQuery中尝试了以下代码，该代码适用于 1,6 ，但对于，8 ，结果为-.8 .如何将其更改为 0.8 数字格式?

  SELECTcolumn_name，REPLACE(column_name，'，'，'.')AS价格FROM table_name 

解决方案

这是一个基于BigQuery格式化数据的有效示例

I have a column with comma separated values such as 1,6 and 8. I tried the following code in a BigQuery and it works for 1,6 but for ,8 the result is -.8. How can I change it to 0.8 number format?

SELECT 
    column_name, 
    REPLACE(column_name,',','.') AS Price 
FROM table_name

解决方案

This is a working example to format your data based on BigQuery formatting syntax

WITH `table_name` AS (
   SELECT '1.6' as column_name UNION ALL
   SELECT '.8'
)

SELECT 
    column_name, 
    format("%g",CAST(REPLACE(column_name,',','.') AS FLOAT64)) AS Price 
FROM `table_name`

This produces the following result:

这篇关于用点将逗号更改为次单位值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！