您可以使用-分隔符连接所有字符串，然后使用正则表达式的两次传递查找前面的字符串，然后查找重复的术语，中间有-，然后是后面的字符串。您需要使用周围的分隔符来确保您匹配完整的术语，并且您需要两次传递，因为每个值中间的分隔符需要为每个术语的左侧和右侧进行匹配，并且Oracle不支持正则表达式中的零宽度前瞻，因此您无法在传递中同时匹配左侧和右侧的重复项。

SELECT TRIM(BOTH '-' FROM
         REGEXP_REPLACE(
           REGEXP_REPLACE(
             REGEXP_REPLACE(
               '-' || D1 || '-' || D2 || '-' || d3 || '-' || D4 || '-',
               '-{2,}',
               '-'
             ),
             '([-_])([^-_]+)-\2([-_])',
             '\1\2\3'
           ),
           '([-_])([^-_]+)-\2([-_])',
           '\1\2\3'
         )
       ) AS result
FROM   table_name

其中，对于样本数据：

CREATE TABLE table_name (D1, D2, D3, D4) AS
SELECT 'AAA-BBB', 'BBB-CCC', 'CCC_DDD', 'DDD-AAA' FROM DUAL UNION ALL
SELECT 'CCC-AAA', 'AAA-BBB', 'BBB-CCC', '-' FROM DUAL UNION ALL
SELECT 'AAA-BBB', '-', '-', 'BBB-CCC' FROM DUAL UNION ALL
SELECT 'AAA-BBB', 'CCC-DDD', 'EEE-FFF', 'GGG-HHH' FROM DUAL

输出：
| 结果|
| --|
| AAA-BBB-CCC_DDD-AAA|
| CCC-AAA-BBB-CCC|
| AAA-BBB-CCC|
| AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH|
如果字符串中的项总是正好是3个字符长，那么可以将其简化为：

SELECT TRIM(BOTH '-' FROM
         REGEXP_REPLACE(
           REGEXP_REPLACE(
             D1 || '-' || D2 || '-' || d3 || '-' || D4,
             '-{2,}',
             '-'
           ),
           '([^-_]{3})-\1',
           '\1'
         )
       ) AS result
FROM   table_name

其输出相同。
fiddle

将4列中的每一列分成左右两部分（给出8列），然后将UNPIVOT这8列分成8行（如果有任何-值，则为更少），并将每行的值与前一行的值进行比较，消除任何相邻的重复项，然后重新聚合行。

SELECT LISTAGG(val, '-') WITHIN GROUP (ORDER BY idx) AS result
FROM   (
  SELECT id,
         CASE
         WHEN LAG(val) OVER (PARTITION BY id ORDER BY idx) = val
         THEN NULL
         ELSE val
         END AS val,
         idx
  FROM   (
    SELECT ROWNUM AS id,
           CASE WHEN d1 = '-' THEN NULL ELSE SUBSTR(d1, 1, 3) END AS d1_1, 
           CASE WHEN d1 = '-' THEN NULL ELSE SUBSTR(d1, 5, 3) END AS d1_2,
           CASE WHEN d2 = '-' THEN NULL ELSE SUBSTR(d2, 1, 3) END AS d2_1,
           CASE WHEN d2 = '-' THEN NULL ELSE SUBSTR(d2, 5, 3) END AS d2_2,
           CASE WHEN d3 = '-' THEN NULL ELSE SUBSTR(d3, 1, 3) END AS d3_1,
           CASE WHEN d3 = '-' THEN NULL ELSE SUBSTR(d3, 5, 3) END AS d3_2,
           CASE WHEN d4 = '-' THEN NULL ELSE SUBSTR(d4, 1, 3) END AS d4_1,
           CASE WHEN d4 = '-' THEN NULL ELSE SUBSTR(d4, 5, 3) END AS d4_2
    FROM   table_name
  )
  UNPIVOT (
    val FOR idx IN (
       d1_1 AS 1,
       d1_2 AS 2,
       d2_1 AS 3,
       d2_2 AS 4,
       d3_1 AS 5,
       d3_2 AS 6,
       d4_1 AS 7,
       d4_2 AS 8
    )
  )
)
WHERE  val IS NOT NULL
GROUP BY id;

其中，对于样本数据：

CREATE TABLE table_name (D1, D2, D3, D4) AS
SELECT 'AAA-BBB', 'BBB-CCC', 'CCC_DDD', 'DDD-AAA' FROM DUAL UNION ALL
SELECT 'CCC-AAA', 'AAA-BBB', 'BBB-CCC', '-' FROM DUAL UNION ALL
SELECT 'AAA-BBB', '-', '-', 'BBB-CCC' FROM DUAL UNION ALL
SELECT 'AAA-AAA', 'AAA-AAA', 'AAA-AAA', 'AAA-AAA' FROM DUAL;

输出：
| 结果|
| --|
| AAA-BBB-CCC-DDD-AAA|
| CCC-AAA-BBB-CCC|
| AAA-BBB-CCC|
| AAA|
fiddle
如果你不想拆分和聚合，那么你可以拆分成列，然后使用多个内嵌视图和CASE表达式来处理不同情况下的字符串合并：

WITH split_data (d1_1, d1_2, d2_1, d2_2, d3_1, d3_2, d4_1, d4_2) AS (
    SELECT CASE WHEN d1 = '-' THEN NULL ELSE SUBSTR(d1, 1, 3) END AS d1_1, 
           CASE WHEN d1 = '-' THEN NULL ELSE SUBSTR(d1, 5, 3) END AS d1_2,
           CASE WHEN d2 = '-' THEN NULL ELSE SUBSTR(d2, 1, 3) END AS d2_1,
           CASE WHEN d2 = '-' THEN NULL ELSE SUBSTR(d2, 5, 3) END AS d2_2,
           CASE WHEN d3 = '-' THEN NULL ELSE SUBSTR(d3, 1, 3) END AS d3_1,
           CASE WHEN d3 = '-' THEN NULL ELSE SUBSTR(d3, 5, 3) END AS d3_2,
           CASE WHEN d4 = '-' THEN NULL ELSE SUBSTR(d4, 1, 3) END AS d4_1,
           CASE WHEN d4 = '-' THEN NULL ELSE SUBSTR(d4, 5, 3) END AS d4_2
    FROM   table_name
),
merge_level_1 (d1_1, d12, d2_2, d3_1, d34, d4_2) AS (
  SELECT d1_1,
         CASE
         WHEN d1_2 != d2_1
         THEN d1_2 || '-' || d2_1
         ELSE COALESCE(d1_2, d2_1)
         END,
         d2_2,
         d3_1,
         CASE
         WHEN d3_2 != d4_1
         THEN d3_2 || '-' || d4_1
         ELSE COALESCE(d3_2, d4_1)
         END,
         d4_2
  FROM   split_data
),
merge_level_2 (d1_1, d1234, d4_2) AS (
  SELECT d1_1,
         CASE
         WHEN d2_2 = d3_1
         THEN d12 || '-' || d2_2 || '-' || d34
         WHEN d2_2 != d3_1
         THEN d12 || '-' || d2_2 || '-' || d3_1 || '-' || d34
         WHEN d12 = d34
         THEN d12
         ELSE d12 || '-' || d34
         END,
         d4_2
  FROM   merge_level_1
)
SELECT TRIM(BOTH '-' FROM d1_1 || '-' || d1234 || '-' || d4_2) AS result
FROM   merge_level_2

其中，对于样本数据：

CREATE TABLE table_name (D1, D2, D3, D4) AS
SELECT 'AAA-BBB', 'BBB-CCC', 'CCC_DDD', 'DDD-AAA' FROM DUAL UNION ALL
SELECT 'CCC-AAA', 'AAA-BBB', 'BBB-CCC', '-' FROM DUAL UNION ALL
SELECT 'AAA-BBB', '-', '-', 'BBB-CCC' FROM DUAL UNION ALL
SELECT 'AAA-BBB', 'CCC-DDD', 'EEE-FFF', 'GGG-HHH' FROM DUAL

输出：
| 结果|
| --|
| AAA-BBB-CCC-DDD-AAA|
| CCC-AAA-BBB-CCC|
| AAA-BBB-CCC|
| AAA-BBB-CCC-DDD-EEE-FFF-GGG-HHH|
fiddle