0

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.)

正规365体育投注How do we do a "safe" SPLIT on fields in Google BigQuery?

2

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

)   

正规365体育投注with output

Row Standard_Name   Standard_Code    
1   MULTUM          123  
2   MULTUM234       null       

instead of Array index 1 is out of bounds (overflow)正规365体育投注 for original query

|improve this answer|||||
  • It is sort of ironic that the question itself contains its own answer. – Gordon Linoff Mar 26 at 23:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy正规365体育投注

Not the answer you're looking for? Browse other questions tagged or ask your own question.