BigQuery - concatenate ignoring NULL

Question

I'm very new to SQL. I understand in MySQL there's the CONCAT_WS function, but BigQuery doesn't recognise this.

I have a bunch of twenty fields I need to CONCAT into one comma-separated string, but some are NULL, and if one is NULL then the whole result will be NULL. Here's what I have so far:

CONCAT(m.track1, ", ", m.track2))) As Tracks,

I tried this but it returns NULL too:

CONCAT(m.track1, IFNULL(m.track2,CONCAT(", ", m.track2))) As Tracks,

Super grateful for any advice, thank you in advance.

user_1144035 · Answer

Unfortunately, BigQuery doesn't support concat_ws(). So, one method is string_agg():

select t.*,
       (select string_agg(track, ',')
        from (select t.track1 as track union all select t.track2) x
       ) x
from t;

Actually a simpler method uses arrays:

select t.*,
       array_to_string([track1, track2], ',')

Arrays with NULL values are not supported in result sets, but they can be used for intermediate results.

user_5221944 · Answer

I have a bunch of twenty fields I need to CONCAT into one comma-separated string

Assuming that these are the only fields in the table - you can use below approach - generic enough to handle any number of columns and their names w/o explicit enumeration

select  
  (select string_agg(col, ', ' order by offset)
  from unnest(split(trim(format('%t', (select as struct t.*)), '()'), ', ')) col with offset
  where not upper(col) = 'NULL'
  ) as Tracks
from `project.dataset.table` t

Below is oversimplified dummy example to try, test the approach

#standardSQL
with `project.dataset.table` as (
  select 1 track1, 2 track2, 3 track3, 4 track4 union all
  select 5, null, 7, 8
)
select  
  (select string_agg(col, ', ' order by offset)
  from unnest(split(trim(format('%t', (select as struct t.*)), '()'), ', ')) col with offset
  where not upper(col) = 'NULL'
  ) as Tracks
from `project.dataset.table` t

with output

BigQuery - concatenate ignoring NULL

2 Answers