Search code examples
google-bigquerydecimalfractions

BigQuery - Converting a mixed fraction to a decimal

How to convert mixed fraction like this one '1 1/2' to a float value (1.5) using Standard SQL of BigQuery?

With few special case examples: 1, 1/2, 1 1 /2, 1 1/ 2, 1 1/2 , 1 1/2 IN, 1\t1/2 .

A large number of rows (>10M), reasonably small number of fraction variations (>.5K).

Solution

  • Below example for BigQuery Standard SQL 

    #standardSQL
CREATE TEMP FUNCTION mix_to_float(str STRING) AS ((
  SELECT 
    IFNULL(SAFE_CAST(arr[SAFE_OFFSET(1)] AS INT64), 0) +
    CAST(SPLIT(arr[SAFE_OFFSET(0)], '/')[OFFSET(0)] AS INT64) / 
    CAST(SPLIT(arr[SAFE_OFFSET(0)], '/')[OFFSET(1)] AS INT64) 
  FROM (SELECT ARRAY_REVERSE(SPLIT(str, ' ')) arr)
));
WITH `project.dataset.table` AS (
  SELECT '1 1/2' mix UNION ALL
  SELECT '8/3'
)
SELECT mix, mix_to_float(mix) as_decimal
FROM `project.dataset.table`

    result would be 

    Row     mix         as_decimal   
1       1 1/2       1.5  
2       8/3         2.6666666666666665

    Above assumes that there is at least fraction part in the string.
    Just quick option - most likely can be optimized further :o)