Any CONCAT() variation that tolerates NULL values?

Question

CONCAT() returns NULL when any value is NULL. I have to use IFNULL() to wrap all fields passed to CONCAT(). Is there a CONCAT() variation that just ignores NULL?

For example:

#standardSQL
WITH data AS (
  SELECT 'a' a, 'b' b, CAST(null AS STRING) nu
)

SELECT CONCAT(a, b, nu) concatenated, ARRAY_TO_STRING([a,b,nu], ',') w_array_to_string
FROM `data`

--->
null

Answer 1

Quick Jam Session on interesting theme in question

There are potentially unlimited combination of real-life use cases here Below are few variations:

#standardSQL
WITH data AS (
  SELECT 'a' a, 'b' b, 'c' c UNION ALL
  SELECT 'y', 'x', NULL UNION ALL
  SELECT 'v', NULL, 'w'
)
SELECT 
  *,
  CONCAT(a, b, c) by_concat, 
  ARRAY_TO_STRING([a,b,c], '') by_array_to_string,
  ARRAY_TO_STRING([a,b,c], '', '?') with_null_placeholder,
  ARRAY_TO_STRING(
    (SELECT ARRAY_AGG(col ORDER BY col DESC) 
      FROM UNNEST([a,b,c]) AS col 
      WHERE NOT col IS NULL)
    , '') with_order
FROM `data`  

The output is:

a   b       c       by_concat   by_array_to_string  with_null_placeholder   with_order   
-   ----    ----    ---------   ------------------  ---------------------   ----------
y   x       null    null        yx                  yx?                     yx   
a   b       c       abc         abc                 abc                     cba  
v   null    w       null        vw                  v?w                     wv   

Answer 2

Use ARRAY_TO_STRING([col1, col2, ...]) instead:

#standardSQL
WITH data AS (
  SELECT 'a' a, 'b' b, CAST(null AS STRING) nu
)

SELECT ARRAY_TO_STRING([a,b,nu], '') w_array_to_string
FROM `data`

--->
ab