Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (Sno, C1, C2, C3, C4) AS
SELECT 1, 'ABC-DEF', 'DEF-ABC', NULL,      NULL      FROM DUAL UNION ALL
SELECT 2, 'DEF-ABC', 'XYZ-DEF', 'ABC-DEF', NULL      FROM DUAL UNION ALL
SELECT 3, 'XYZ-DEF', 'DEF-ABC', 'ABC-DEF', 'GHI-XYZ' FROM DUAL;
3 rows affected

SELECT sno,
       c1
       || CASE
          WHEN c2 IS NULL
          THEN NULL
          WHEN SUBSTR(c1, -3) = SUBSTR(c2, 1, 3)
          THEN SUBSTR(c2, 4)
          ELSE '_' || c2
          END
       || CASE
          WHEN c3 IS NULL
          THEN NULL
          WHEN SUBSTR(c2, -3) = SUBSTR(c3, 1, 3)
          THEN SUBSTR(c3, 4)
          ELSE '_' || c3
          END
       || CASE
          WHEN c4 IS NULL
          THEN NULL
          WHEN SUBSTR(c3, -3) = SUBSTR(c4, 1, 3)
          THEN SUBSTR(c4, 4)
          ELSE '_' || c4
          END AS combined
FROM   table_name;


SNO
COMBINED


1
ABC-DEF-ABC

2
DEF-ABC_XYZ-DEF_ABC-DEF

3
XYZ-DEF-ABC-DEF_GHI-XYZ


SELECT sno,
       REGEXP_REPLACE(
          c1
          || CASE WHEN c2 IS NULL THEN NULL ELSE '_' || c2 END
          || CASE WHEN c3 IS NULL THEN NULL ELSE '_' || c3 END
          || CASE WHEN c4 IS NULL THEN NULL ELSE '_' || c4 END,
          '([^-_]{3})_\1',
          '\1'
       ) AS combined
FROM   table_name;


SNO
COMBINED


1
ABC-DEF-ABC

2
DEF-ABC_XYZ-DEF_ABC-DEF

3
XYZ-DEF-ABC-DEF_GHI-XYZ

SNO	COMBINED
1	ABC-DEF-ABC
2	DEF-ABC_XYZ-DEF_ABC-DEF
3	XYZ-DEF-ABC-DEF_GHI-XYZ