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

Question

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?

Answer 1

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