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:

CodePudding user response：

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
)

CodePudding user response：

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.