How to concat values without NULL results (Snowflake)

user_13599372 0
Viewed 3759

I'm trying to concat not null values in Snowflake.

My current code:

            CONCAT(
                CASE WHEN Red='TRUE' THEN 'Red, ' ELSE '' END ||
                CASE WHEN Blue='TRUE' THEN 'Blue, ' ELSE '' END ||
                CASE WHEN Black='TRUE' THEN 'Black, ' ELSE '' END ||
                CASE WHEN White='TRUE' THEN 'White, ' ELSE '' END)

Return: 
Red, White, Black,
Black,
...

Want:
Red, White, Black
Black

In Mysql, I would use Concat_WS, but in Snowflake if one value is NULL, NULL will be in Return:

            CONCAT_WS(', ',
                CASE WHEN Red='TRUE' THEN 'Red' ELSE NULL END,
                CASE WHEN Blue='TRUE' THEN 'Blue' ELSE NULL END,
                CASE WHEN Black='TRUE' THEN 'Black' ELSE NULL END,
                CASE WHEN White='TRUE' THEN 'White' ELSE NULL END)
snowflake-cloud-data-platform concat sql string
3 Answers

A bit late, but I had the same problem and found a different solution:

As was rightly pointed out before CONCAT and WS_CONCAT always return NULL when any argument to them is NULL.

But, as Snowflake has a concept of ARRAYs (i.e. support for semi-structured data), i.e. we can do the following:

  1. Collect the raw values into an array with array_construct
  2. Remove NULL values from the array with array_compact
  3. Use array_to_string to concatenate the remaining array items into a string

Here's the modifief code, using arrays:

select array_to_string(array_compact(array_construct( 
          CASE WHEN Red = 'TRUE' THEN 'Red' ELSE NULL  END,
          CASE WHEN Blue = 'TRUE' THEN 'Blue' ELSE NULL END, 
          CASE WHEN Black = 'TRUE' THEN 'Black' ELSE NULL END,
          CASE WHEN White = 'TRUE' THEN 'White' ELSE NULL END)), ', ') as result
FROM values 
( true, true, false, false ),
( true, false, false, true ),
( false, false, true, false ),
( false, true, false, true ),
( true, true, true, true )
v(red,blue,black,white);

EDIT: You can also use array_construct_compact, which is a condensed version of array_construct and array_compact

user_80768710

You can use RTRIM to remove ending comma:

select RTRIM(CONCAT( 
          CASE WHEN Red = 'TRUE' THEN 'Red, ' ELSE ''  END,
          CASE WHEN Blue = 'TRUE' THEN 'Blue, ' ELSE '' END, 
          CASE WHEN Black = 'TRUE' THEN 'Black, ' ELSE '' END,
          CASE WHEN White = 'TRUE' THEN 'White, ' ELSE '' END ),', ' ) result
FROM values 
( true, true, false, false ),
( true, false, false, true ),
( false, false, true, false ),
( false, true, false, true ),
( true, true, true, true )
v(red,blue,black,white);

+-------------------------+
| RESULT                  |
+-------------------------+
| Red, Blue               |
| Red, White              |
| Black                   |
| Blue, White             |
| Red, Blue, Black, White |
+-------------------------+

You may also consider to convert empty string to NULL using NULLIF function:

select NULLIF( RTRIM(CONCAT( 
          CASE WHEN Red = 'TRUE' THEN 'Red, ' ELSE ''  END,
          CASE WHEN Blue = 'TRUE' THEN 'Blue, ' ELSE '' END, 
          CASE WHEN Black = 'TRUE' THEN 'Black, ' ELSE '' END,
          CASE WHEN White = 'TRUE' THEN 'White, ' ELSE '' END ),', ' ), '' )result
FROM values 
( false, false, false, false )
v(red,blue,black,white);
user_125509650

I understand that your expression does quite what you want, but you need to remove the trailing comma. You can use TRIM() for this:

TRIM(', ' FROM 
       CASE WHEN Red   = 'TRUE' THEN 'Red, '   ELSE '' END  
    || CASE WHEN Blue  = 'TRUE' THEN 'Blue '   ELSE '' END 
    || CASE WHEN Black = 'TRUE' THEN 'Black, ' ELSE '' END 
    || CASE WHEN White = 'TRUE' THEN 'White, ' ELSE '' END
)

Note that there is no point using both || and CONCAT(): I removed the surrounding CONCAT().

user_106767160
Related