Join strings in Oracle like concat_ws in SQL Server

Question

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?

Does this answer your question? Is there an equivalent to concat_ws in oracle? — Thom A
– Thom A ♦, Commented Sep 2, 2021 at 11:26
In terms of in both, unfortunately every dialect of SQL can be very different. For example even basic concatenation in T-SQL and PL/SQL is quite different, with them using + and || respectively. If you are working with multiple dialects, there are few times where you will have a query that is transferable without some kind of minimal change. — Thom A
– Thom A ♦, Commented Sep 2, 2021 at 11:28

Gordon Linoff · Accepted Answer · 2021-09-02 11:52:47Z

2

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.

answered Sep 2, 2021 at 11:52

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TimLer · Accepted Answer · 2021-09-02 11:43:01Z

1

select c1 || nvl2(c2, '-'||c2,c2) || nvl2(c3, '-'||c3,c3) || nvl2(c4, '-'||c4,c4)
from mytable

test it here

edited Sep 2, 2021 at 11:43

answered Sep 2, 2021 at 11:37

TimLer

1,3604 silver badges16 bronze badges

Comments

Littlefoot · Accepted Answer · 2021-09-02 11:43:44Z

One option is to

concatenate all columns with - as a separator, and then
remove double (triple, ...) - signs (with regexp) and
remove leading/trailing - signs (with trim)

Something like this:

SQL> with test (c1, c2, c3, c4) as
  2    (select 'a' , 'b' , 'c' ,  'd' from dual union all
  3     select 'a' , 'b' , null, null from dual union all
  4     select 'a' , null, null, null from dual union all
  5     select 'a' , null, 'c' , null from dual union all
  6     select null, null, 'c' , 'd'  from dual
  7    )
  8  select
  9    c1, c2, c3, c4,
 10    --
 11    trim(both '-' from regexp_replace(c1 ||'-'|| c2 ||'-'|| c3 ||'-'|| c4, '-+', '-')) result
 12  from test;

C1 C2 C3 C4 RESULT
-- -- -- -- --------------------
a  b  c  d  a-b-c-d
a  b        a-b
a           a
a     c     a-c
      c  d  c-d

SQL>

Collectives™ on Stack Overflow

Join strings in Oracle like concat_ws in SQL Server

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related