How to get a sum of currency values, when stored as a string?

Question

I'll preface this question with that I'm extremely new to learning SQL and so much of this is over my head... but I'm trying! haha

Ultimately, what I want is super simple (famous last words), to sum up the top 200 merchants estimated sales values by country. However, the wrinkle I ran into this evening is that the currency that I want to sum, is stored as a string, in various currency types.

Is there a way to convert those currency values to a number, so I can effectively sum those up?

Here's a sample of the query I was hoping to use in BigQuery, when I discovered the string issue and I got blocked:

select sum (estimated_monthly_sales) as top_200_monthly_sales
from (
select merchant_name,
sum (estimated_monthly_sales) as monthly_sales_est
from `storesList`
 where country_code = "AT"
  group by 1
  order by monthly_sales_est DESC
  limit 200)

Here's a sample table:

merchant_name	estimated_monthly_sales	country_code
A Store	$ 1,450.99	US
B Store	EUR €936.97	AT
C Store	CZK 2,879.97 Kč	CZ
D Store	$ 2,631.99	US
E Store	EUR €1,234.56	AT
F Store	CZK 1,845.97 Kč	CZ

score 0 · Accepted Answer · answered Nov 03 '21 at 05:30

0

Is there a way to convert those currency values to a number, so I can effectively sum those up?

Below is an example of how to do so

select round(sum(monthly_sales_est), 2) as top_200_monthly_sales
from (
  select merchant_name,
    sum (cast(regexp_replace(estimated_monthly_sales, r'[^0-9.]', '') as float64)) as monthly_sales_est
  from `storesList`
  where country_code = "AT"
  group by 1
  order by monthly_sales_est DESC
  limit 200
)

answered Nov 03 '21 at 05:30

Mikhail Berlyant

165,386
8
154
230

Thank you, this worked like a charm!!! Follow-up question if you don't mind, my result is in scientific notation. Is there a simple way to have the output in regular number format? – Aut Mur Nov 03 '21 at 05:51

s.ch · Answer 2 · 2021-11-03T05:40:15.723

I think Its help you.

For getting Numeric currency you first create function like below and use in your select query as below..

 Create FUNCTION dbo.Getcurrency
 (
   @numericurrency VARCHAR(256)
 )
 RETURNS VARCHAR(256)
 AS
 BEGIN
   DECLARE @intAlpha INT
   SET @intAlpha = PATINDEX('%[^0-9,.]%', @numericurrency)
   BEGIN
     WHILE @intAlpha > 0
     BEGIN
       SET @numericurrency = STUFF(@numericurrency, @intAlpha, 1, '' )
       SET @intAlpha = PATINDEX('%[^0-9,.]%', @numericurrency )
     END
   END
   RETURN ISNULL(@numericurrency,0)
 END
 GO

select dbo.Getcurrency(estimated_monthly_sales) from [Table_name]

I take REF:Query to get only numbers from a string

in this only read number not read specific currency
But above code i Modified with changes.

How to get a sum of currency values, when stored as a string?

2 Answers2