sqlstringoracle-databaseconcatenation

Join strings in Oracle like concat_ws in SQL Server


I have a table with multiple string columns I would like to join together with a separator.

c1 c2 c3 c4
a b c d
a b
a

The result for that should be

'a-b-c-d'
'a-b'
'a'

In SQL Server I just do

select concat_ws('-', c1, c2, c3, c4) from my_table

In Oracle I can do

SELECT COALESCE(c1, '') || 
  CASE WHEN c2 IS NULL THEN '' ELSE '-' || c2 END || 
  CASE WHEN c3 IS NULL THEN '' ELSE '-' || c3 END ||
  CASE WHEN c4 IS NULL THEN '' ELSE '-' || c4 END  
FROM my_table

Is there a better solution in Oracle or even one that works for both - SQL Server and Oracle?


Solution

  • A version that works in both Oracle and SQL Server is tricky because the only string concatenation function available is concat() with two arguments. But, you can do:

    select trim('-' from
            concat(coalesce(c1, ''),
                  concat(case when c2 is null then '' else concat('-', c2) end,
                         concat(case when c3 is null then '' else concat('-', c3) end,
                                case when c4 is null then '' else concat('-', c4) end
                               )
                        )
                 ))
    

    Here are the two db<>fiddles for SQL Server and Oracle.