Google BigQuery Query to convert dot "." to comma ","

Question

I have a Query that works (not made by me), but I would like to add a way to convert a few columns with numbers with dots "." to commas "," as I am from Europe and cannot work with dot notation inside Google Sheets.

Column names where I need to convert from dot "." to comma "," to be able to use it as a number in Google Sheets:

item_price  
item_tax    
shipping_price  
shipping_tax    
gift_wrap_price 
gift_wrap_tax   
item_promotion_discount 
ship_promotion_discount

Here is my actual Query:

with raw as (
SELECT
amazon_order_id AS Ref_Commande,
TIMESTAMP(purchase_date) Date_Achat,
date(TIMESTAMP(purchase_date)) Date,
extract(hour from TIMESTAMP(purchase_date)) Heures,
extract(minute from TIMESTAMP(purchase_date)) Minutes,
extract(second from TIMESTAMP(purchase_date)) Secondes,
case when extract(dayofweek from TIMESTAMP(purchase_date)) = 1 then '7.Dimanche'
when extract(dayofweek from TIMESTAMP(purchase_date)) = 2 then '1.Lundi'
when extract(dayofweek from TIMESTAMP(purchase_date)) = 3 then '2.Mardi'
when extract(dayofweek from TIMESTAMP(purchase_date)) = 4 then '3.Mercredi'
when extract(dayofweek from TIMESTAMP(purchase_date)) = 5 then '4.Jeudi'
when extract(dayofweek from TIMESTAMP(purchase_date)) = 6 then '5.Vendredi'
when extract(dayofweek from TIMESTAMP(purchase_date)) = 7 then '6.Samedi'
end as jours,

TIMESTAMP(last_updated_date) Date_Update,
order_status AS Status_Commande,
fulfillment_channel AS Expediteur, 
sales_channel AS Marketplace,
product_name AS Ref_Articles,
sku AS SKU,
asin AS ASIN,
item_status AS Status_Expedition,
quantity AS Quantite_Articles,
item_price AS Montant_Articles,
item_tax AS Montant_TVA,
shipping_price AS Montant_Port,
shipping_tax AS Montant_TVA_Port,
gift_wrap_price AS Montant_Emballage_Cadeau,
gift_wrap_tax AS Montant_TVA_Emballage_Cadeau,
item_promotion_discount AS Montant_Promotion,
ship_promotion_discount AS Montant_Promotion_Port,
ship_city AS Ville,
ship_state AS Etat,
ship_postal_code AS Code_Postal,
ship_country AS Pays,
promotion_ids AS Ref_Promotion,
is_business_order AS Client_Business,
buyer_company_name AS Nom_Client_Business,
row_number() over (partition by amazon_order_id,sku order by last_updated_date desc) rn 
FROM
`rankup-271414.RANKUP.Hoopzi_Seller_FR_FlatFileAllOrdersReportbyLastUpdate`
)

select *
from raw
where rn = 1
and Marketplace != 'Non-Amazon'
and Status_Commande != 'Cancelled'
order by Date_Achat desc

user_16929145 · Answer

As you can see in this example, I replaced the dot with a comma.

with numbers as (
 SELECT 123456.1 AS input UNION ALL
 SELECT 1234567.12 AS input UNION ALL
 SELECT 12345678.123 AS input
)
 
SELECT
 input,
 REPLACE(cast(input as string), '.', ',') as formatted
FROM numbers

In your case you will use only the field you want to change the comma for a dot. Like these examples:

 REPLACE(cast(item_price as string), '.', ',') as Montant_Articles
 REPLACE(cast(item_tax as string), '.', ',') as Montant_TVA
 REPLACE(cast(shipping_price as string), '.', ',') as Montant_Port
 REPLACE(cast(shipping_tax as string), '.', ',') as Montant_TVA_Port
 REPLACE(cast(gift_wrap_price as string), '.', ',') as Montant_Emballage_Cadeau
 REPLACE(cast(gift_wrap_tax as string), '.', ',') as Montant_TVA_Emballage_Cadeau
 REPLACE(cast(item_promotion_discount as string), '.', ',') as Montant_Promotion
 REPLACE(cast(ship_promotion_discount as string), '.', ',') as Montant_Promotion_Port

You can see the result.

Google BigQuery Query to convert dot "." to comma ","

1 Answers