My decimal numbers use commas, not periods

I imported data with Blendo from a google sheet. In Germany, we separate decimals with a comma and not with a period. For example, instead of three and a half being 3.5 we write it as 3,5 instead.

I think that the database does not recognize my decimal numbers because it’s expecting periods but receiving commas. The fields are saved in the data warehouse as text strings, which means we can’t use sum functions and other numeric operations.

What can I do to fix this situation?

Since your data is imported via Blendo, your comma-separated decimals will be stored as varchar.

I can suggest two possible approaches:

  • Create a view above the desired tables. In the view definition, use replace (from comma to period) on the comma-separated columns and then cast the values into a numerical data type. A query above this view will allow you to use any aggregation function, such as sum, max, or min.

  • Use above approach at the query level. For example:

     SELECT
       SUM(CAST(replace(<column_name>,',','.') AS DOUBLE PRECISION))
     FROM
       table;
    

In Chart.io, the approach would be to use “SQL Mode” (when you click on “Edit Chart Data”), and then you can use SQL with replace and cast functions.

Let us know if you need assistance in defining your query.