How to do a "safe" SPLIT in Google BigQuery?

user_1174302 0
Viewed 3607

The following code on Google BigQuery throws an index out of bounds error. 

SELECT 
  SPLIT(CONCEPT_CKI, "!")[OFFSET(0)] AS Standard_Name
  ,SPLIT(CONCEPT_CKI, "!")[OFFSET(1)] AS Standard_Code

FROM 
(
      SELECT "MULTUM!123" AS CONCEPT_CKI
      UNION ALL
      SELECT "MULTUM234" AS CONCEPT_CKI

)

We would like the split to work on fields it can work on - and simply yield a NULL value for the second field when it cannot. (i.e. somewhat similar to how the SAFE_CAST command gives a NULL if it cannot do the cast.)

How do we do a "safe" SPLIT on fields in Google BigQuery?

google-bigquery sql
1 Answers

Use SAFE_OFFSET as in below example 

SELECT 
  SPLIT(CONCEPT_CKI, "!")[OFFSET(0)] AS Standard_Name
  ,SPLIT(CONCEPT_CKI, "!")[SAFE_OFFSET(1)] AS Standard_Code

FROM 
(
      SELECT "MULTUM!123" AS CONCEPT_CKI
      UNION ALL
      SELECT "MULTUM234" AS CONCEPT_CKI

)

with output 

Row Standard_Name   Standard_Code    
1   MULTUM          123  
2   MULTUM234       null

instead of Array index 1 is out of bounds (overflow) for original query

user_52219440
Related