CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.

mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
-> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
-> 'First name,Last Name'

CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

SELECT column1 || ',' || column2 || ',' ||.... FROM DUAL

I know in Oracle only the manual way

SELECT column1 || ',' || column2 || ',' ||.... FROM DUAL

is there a shortcut?

Note : You need to take care of NULL in either of the cases.

Quote:

Note : You need to take care of NULL in either of the cases.

For what? Explain.

CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

SQL> select concat('hi','') from dual;

CO
--
hi

SQL> select concat('hi',null) from dual;

CO
--
hi

SQL> select concat(null,null) from dual

C
-


SQL> select concat('','')from dual

C
-


SQL>

SQL> select '1' || ',' || null || ',' || '3' res from dual;
RES
----
1,,3

So OP's statement "CONCAT_WS() does not skip empty strings" is not valid in Oracle.

it does skip any NULL values

SELECT CONCAT('"',C1,'","',C2,'","',...,'"') FROM TBL

SQL> create table t(a varchar2(10), b varchar2(10), c varchar2(10));

Table created.

SQL> insert into t select 'a', 'b', 'c' from dual;

1 row created.

SQL> select concat(a,',',b,',',c) from t;
select concat(a,',',b,',',c) from t
       *
ERROR at line 1:
ORA-00909: invalid number of arguments


SQL> ed
Wrote file afiedt.buf

  1* SELECT CONCAT('"',a,'","',b,'","',c,'"') FROM t
SQL> /
SELECT CONCAT('"',a,'","',b,'","',c,'"') FROM t
       *
ERROR at line 1:
ORA-00909: invalid number of arguments

SQL> select '1' || ',' || null || ',' || '3' res from dual;
RES
----
1,,3

We have 3 fields with second one as null (empty).

Quote:

So OP's statement "CONCAT_WS() does not skip empty strings" is not valid in Oracle.

Yes, it is as || does skip the NULL but

Quote:

it does skip any NULL values

is not but can't be given that CONCAT_WS is more, for a single row multiple expressions) like LISTAGG (for a single expression on multiple rows) (as LISTAGG skips NULL) than like CONCAT or ||.

SQL> select '1' || ',' || null || ',' || '3' res from dual;

RES
----
1,,3

SQL> ed
Wrote file afiedt.buf

  1* select '1' || ',' ||''|| ',' || '3' res from dual
SQL> /

RES
----
1,,3