SQLite3 doesn’t have concat_ws, looking for workaround

concatsqlite

I'm trying to concatenate some columns and I would like to use space as my output string delimiter.

Basically concat_ws would be a perfect fit, but it doesn't exist for SQLite3.

I'm inside a SELECT already and I'd like to end up with a string that looks like this:

col1_val col2_val col3_val

I have col1, col2, and col3 as my columns, and they may have empty strings as values which I'd not want to delimit.

col1, is safely expected to exist, to not be NULL, and not be empty.
However either col2 and col3 might be empty, and I'd thus want to skip.

I've tried a few different ideas, but all of them seem to end up with double delimiters when a string is empty.

So some expected results:

col1_val
col1_val col2_val
col1_val col3_val
col1_val col2_val col3_val

Best Answer

You can use CASE:

SELECT coalesce(col1, '')
       || CASE
            WHEN coalesce(col1, '') <> ''
                 AND (coalesce(col2, '') <> ''
                       OR coalesce(col3, '') <> '')
              THEN ' '
            ELSE
              ''
          END
       || coalesce(col2, '')
       || CASE
            WHEN coalesce(col2, '') <> ''
                 AND coalesce(col3, '') <> ''
              THEN ' '
            ELSE
              ''
          END
       || coalesce(col3, '')
       FROM elbat;