Search code examples
sqlgoogle-bigqueryoffset

Condition OFFSET on SPLIT in SQL

I am using SQL on Google Big Query.

I have a column MyColumn which value is in this format :

Text0;Text1;Text2;Text3;Text4;T12=12/T26=5/T13=1/T55=ABC

I need to fetch the value of T13 ("1" in this case).

The code I have done so far is this :

 SELECT MyColumn,
    SPLIT(MyColumn, ';')[SAFE_OFFSET(1)] Text1,
    SPLIT(MyColumn, ';')[SAFE_OFFSET(2)] Text2,
    SPLIT(MyColumn, ';')[SAFE_OFFSET(1)] Text3,
    SPLIT(MyColumn, ';')[SAFE_OFFSET(1)] Text4,
    REPLACE(SPLIT(SPLIT(MyColumn, ';')[SAFE_OFFSET(5)],"/")[SAFE_OFFSET(2)],"T13=","") T13
 FROM MyTable

This code works if T13 is positioned the 3rd in the list. But I have values where it is not. For example :

Text0;Text1;Text2;Text3;Text4;T13=1/T55=ABC

Text0;Text1;Text2;Text3;Text4;T12=12/T26=5/T55=ABC/T13=1

Once I've done the SPLIT by "/", how can I dynamically detect which position T13 is and fetch its value ? Is a dynamic OFFSET possible ?

Thank you

Solution

  • Use regexp_extract():

    select regexp_extract(mycolumn, 'T13=([^/;]+)')
from (select 'Text0;Text1;Text2;Text3;Text4;T12=12/T26=5/T13=1/T55=ABC' as mycolumn union all 
      select 'Text0;Text1;Text2;Text3;Text4;T13=1/T55=ABC' union all
      select 'Text0;Text1;Text2;Text3;Text4;T12=12/T26=5/T55=ABC/T13=1'
    ) t