Casting all elements of an Array in BigQuery

user_546061 0
Viewed 4701

My input is a string containing an unknowm number of elements: "1,2,3". From that, I'd like to get an array of integers.

Using SPLIT will get me an array of strings. How to convert this to an array of integers.

My best guess is a user-defined function.

google-bigquery casting arrays
1 Answers

Below is for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.yourTable` AS (
  SELECT '1,2,3,4,5' str UNION ALL
  SELECT '12,34,56,78'
)
SELECT ARRAY(SELECT CAST(num AS INT64) FROM UNNEST(SPLIT(str)) num) arr
FROM `project.dataset.yourTable`
user_52219440
Related