Concatenate values in oracle

Question

I have to concatenate column a,b,c using '-' like a-b-c. If a or b or c is null then we should not consider this column value.

Example:

select A,B,C,
case 
  when A is not null and B is not null and C is not null then
  A ||'-'||B ||'-'|| C
  when A is not null and B is not null then 
A||'-'||B
when A is not null and C is not null then 
A||'-'||C
when B is not null and C is not null then 
B||'-'||C
when A is null and B is null and C is not null then 
C
when A is null and C is null and B is not null  then 
B
when B is null and C is null and A is not null then 
A
end
  TEXT from
table1

Please suggest can we implement this logic in some other way

Answer 1

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-3

1
1-2

SQL>

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-3
1
1-2

SQL>

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-3
1
1-2
1-3

SQL>

Answer 2

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;

Answer 3

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-C
A-C
C

B
A

6 rows selected