CODEWITHSUNDEEP
sqlstringsplitgoogle-bigquery
Lucasaudati11
Lucasaudati11

Reputation: 577

SQL SPLIT a string by means of delimiters but reporting NULL when there is missing values

I am working with BigQuery and I want to split this following kind of string: ;%20kostenlos%20%20online%20%20game;b;g;177393617893; into 5 different values. The code I am using is this following:

   FROM (select *, FIRST(SPLIT(clicks_table.affiliate_sub5, ';')) part1,
   NTH(2, SPLIT(clicks_table.affiliate_sub5, ';')) part2,
   NTH(3, SPLIT(clicks_table.affiliate_sub5, ';')) part3,
   NTH(4, SPLIT(clicks_table.affiliate_sub5, ';')) part4,
   NTH(5, SPLIT(clicks_table.affiliate_sub5, ';')) part5

What I am getting stuck into is this: for some strings, some values are missing, and when it happens, that code is not reporting NULL for that piece, but the following piece of string. As instance:

;%20kostenlos%20%20online%20%20game;b;;177393617893;

then it will be splitted this way:

   part 1 = %20kostenlos%20%20online%20%20game
   part 2 = b
   part 3 = 177393617893 (instead of NULL)
   part 4 = blank (instead of 177393617893)
   part 5 = blank (and that's fine)

How to avoid that?

Upvotes: 1

Views: 326

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

for BigQuery Legacy SQL

#legacySQL
SELECT *, 
FIRST(SPLIT(REPLACE(clicks_table.affiliate_sub5, ';;', '; ;'), ';')) part1,
NTH(2, SPLIT(REPLACE(clicks_table.affiliate_sub5, ';;', '; ;'), ';')) part2,
NTH(3, SPLIT(REPLACE(clicks_table.affiliate_sub5, ';;', '; ;'), ';')) part3,
NTH(4, SPLIT(REPLACE(clicks_table.affiliate_sub5, ';;', '; ;'), ';')) part4,
NTH(5, SPLIT(REPLACE(clicks_table.affiliate_sub5, ';;', '; ;'), ';')) part5
FROM clicks_table

for BigQuery StandardSQL 

#standardSQL
SELECT *, 
  SPLIT(clicks_table.affiliate_sub5, ';')[SAFE_OFFSET(1)] AS part1,
  SPLIT(clicks_table.affiliate_sub5, ';')[SAFE_OFFSET(2)] AS part2,
  SPLIT(clicks_table.affiliate_sub5, ';')[SAFE_OFFSET(3)] AS part3,
  SPLIT(clicks_table.affiliate_sub5, ';')[SAFE_OFFSET(4)] AS part4,
  SPLIT(clicks_table.affiliate_sub5, ';')[SAFE_OFFSET(5)] AS part5
FROM  clicks_table

Upvotes: 2

Related Questions