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?
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.