Concatenate values in oracle

You don't have to make it so complex. NULL would be anyway handled implicitly and won't be considered, you just need to take care of the delimiter that you are using to concatenate the strings. TRIM should do the job.

SQL> WITH DATA AS(  2  SELECT 1 A, 2 b, 3 c FROM dual UNION ALL  3  SELECT NULL A, NULL b, NULL c FROM dual UNION ALL  4  SELECT 1 A, NULL b, NULL c FROM dual UNION ALL  5  SELECT 1 A, 2 b, NULL c FROM dual  6  )  7  SELECT TRIM(both '-' FROM A||'-'||b||'-'||c) str FROM DATA;STR----------------------------------------------------------------1-2-311-2SQL>

If you want to exclude the NULL values, then add a NOT NULL filter to the predicate.

SQL> WITH DATA AS(  2  SELECT 1 A, 2 b, 3 c FROM dual UNION ALL  3  SELECT NULL A, NULL b, NULL c FROM dual UNION ALL  4  SELECT 1 A, NULL b, NULL c FROM dual UNION ALL  5  SELECT 1 A, 2 b, NULL c FROM dual  6  )  7  SELECT str  8  FROM  9    ( SELECT TRIM(BOTH '-' FROM A||'-'||b||'-'||c) str FROM DATA 10    ) 11  WHERE str IS NOT NULL;STR-------------------------------------------------------------------1-2-311-2SQL>

Update case when the middle column is NULL and other columns are not null.

SQL> WITH DATA AS(  2  SELECT 1 A, 2 b, 3 c FROM dual UNION ALL  3  SELECT NULL A, NULL b, NULL c FROM dual UNION ALL  4  SELECT 1 A, NULL b, NULL c FROM dual UNION ALL  5  SELECT 1 A, 2 b, NULL c FROM dual UNION ALL  6  SELECT 1 A, NULL b, 3 c FROM dual  7  )  8  SELECT REPLACE(str, '--', '-') str  9  FROM 10    ( SELECT TRIM(BOTH '-' FROM A||'-'||b||'-'||c) str FROM DATA 11    ) 12  WHERE str IS NOT NULL;STR-------------------------------------------------------------------1-2-311-21-3SQL>

A more generic solution is to write a function for this:

CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(1000);CREATE OR REPLACE FUNCTION JoinTable(    TAB IN VARCHAR_TABLE_TYPE,     Joiner IN VARCHAR2 DEFAULT '-') RETURN VARCHAR2 IS   res VARCHAR2(30000);BEGIN    IF TAB IS NULL THEN         RETURN NULL;     END IF;    IF TAB.COUNT = 0 THEN         RETURN NULL;     END IF;    FOR i IN TAB.FIRST..TAB.LAST LOOP        IF TAB(i) IS NOT NULL THEN            res := res ||Joiner||TAB(i);        END IF;    END LOOP;    RETURN REGEXP_REPLACE(res, '^'||Joiner);END JoinTable;SELECT JoinTable(VARCHAR_TABLE_TYPE(A,B,C)) from Table1;

Why not use function nvl2?

select rtrim(nvl2(a, a||'-', '')||nvl2(b, b||'-', '')||nvl2(c, c, ''), '-') from table1

Test:

with table1 as(  select 'A' a, 'B' b, 'C' c from dual union all  select 'A',  null, 'C'  from dual union all  select null, null, 'C'  from dual union all  select null, null, null from dual union all  select null, 'B', null from dual union all  select 'A',  null, null from dual  )select rtrim(nvl2(a, a||'-', '')||nvl2(b, b||'-', '')||nvl2(c, c, ''), '-') col  from table1

Result:

COL-----A-B-CA-CCBA6 rows selected