Oracle多列字符串拼接：匹配重叠字符合并，否则下划线连接

问题分析 #

需要实现以下字符串拼接逻辑：

• 忽略空列，按原列顺序拼接非空列
• 相邻列匹配规则：
  • 若前一列最后3个字符与后一列前3个字符一致：用-连接，并去除重复的3个字符
  • 若不匹配：用_连接，保留双方完整内容

修改后的SQL实现 #

WITH unpivoted AS (
  -- 将每行的非空列转换为多行，保留列的原始顺序
  SELECT sno, column_value AS col, rn
  FROM t1
  CROSS APPLY (
    SELECT column_value, ROWNUM rn
    FROM TABLE(SYS.ODCIVARCHAR2LIST(C1, C2, C3, C4))
    WHERE column_value IS NOT NULL
  )
),
recursive_concat AS (
  -- 递归基准：取每个Sno的第一列作为初始拼接结果
  SELECT sno, col AS concat_str, rn
  FROM unpivoted
  WHERE rn = 1
  UNION ALL
  -- 递归拼接：逐列判断匹配规则，选择对应连接方式
  SELECT uc.sno, 
         rc.concat_str || 
         CASE 
           WHEN SUBSTR(rc.concat_str, -3) = SUBSTR(uc.col, 1, 3) THEN '-' || SUBSTR(uc.col, 4)
           ELSE '_' || uc.col
         END AS concat_str,
         uc.rn
  FROM recursive_concat rc
  JOIN unpivoted uc ON rc.sno = uc.sno AND uc.rn = rc.rn + 1
)
-- 取每个Sno的最终拼接结果
SELECT sno, concat_str AS Concatenated_Data
FROM recursive_concat
WHERE rn = (SELECT MAX(rn) FROM unpivoted WHERE sno = recursive_concat.sno);

代码逻辑说明 #

1. Unpivoted CTE：

   • 使用SYS.ODCIVARCHAR2LIST将C1-C4列转为集合，再通过CROSS APPLY展开为多行
   • 过滤空值，用ROWNUM标记列的原始顺序，保证拼接顺序和原表列顺序一致

2. Recursive_Concat CTE：

   • 基准查询：获取每个Sno的第一个非空列作为初始字符串
   • 递归查询：遍历后续每一列，通过SUBSTR函数判断前后字符串的匹配情况：
     • 匹配时：仅拼接当前列从第4位开始的内容（去掉重复的3个字符），用-连接
     • 不匹配时：直接用_连接整个当前列

3. 最终查询：

   • 选取每个Sno对应的最大行号（即最后一次拼接的结果），得到最终的拼接字符串

验证结果 #

针对给定的测试数据，执行上述SQL后将得到预期结果：

SNo Concatenated_Data
1   ABC-DEF-ABC
2   DEF-ABC_XYZ-DEF_ABC-DEF
3   XYZ-DEF-ABC-DEF_GHI-XYZ

内容的提问来源于stack exchange，提问作者Richard