BigQuery - concatenate ignoring NULL


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.

Gordon Linoff

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at


Login to comment