Oracle SQL实现含同义不同写法的两列等值比较并新增标记列

核心思路是先对两个字段做统一标准化预处理，把同义的不同书写形式替换为相同的标准值，再做等值比较输出标识。

简单场景方案（同义词规则少） #

如果你的同义异形词规则数量不多（比如仅常见地址缩写），可以直接嵌套REPLACE函数做替换，示例代码如下：

SELECT
  Column_1,
  Column_2,
  CASE
    WHEN
      -- 对Column_1做标准化：先去前后空格，再把所有Ave替换为Avenue，可按需嵌套更多REPLACE
      REPLACE(TRIM(Column_1), 'Ave', 'Avenue')
      =
      -- Column_2做完全相同的标准化处理
      REPLACE(TRIM(Column_2), 'Ave', 'Avenue')
    THEN 'Yes'
    ELSE 'No'
  END AS "Equal?"
FROM 你的实际表名;

优化建议 #

• 替换时可以给关键词前后加空格，比如写成REPLACE(..., ' Ave ', ' Avenue ')，避免误替换到单词内部包含对应字符的内容
• 如需兼容大小写差异，可以在外层包裹UPPER()或者LOWER()函数统一大小写后再比较
• 可按需补充更多REPLACE嵌套，比如新增St转Street、Rd转Road等规则

复杂场景方案（同义词规则多） #

如果你的同义替换规则超过5个，建议单独建一张同义词映射表SYNONYM_MAP，字段包含SHORT_FORM（缩写/异形写法）、LONG_FORM（标准写法），后续通过递归批量做替换，避免嵌套过多REPLACE导致语句难以维护，示例代码如下：

-- 递归做批量标准化替换
WITH std_col1 AS (
  SELECT
    原始表主键ID,
    Column_1 AS original_val,
    REPLACE(Column_1, SHORT_FORM, LONG_FORM) AS std_val,
    ROW_NUMBER() OVER(PARTITION BY 原始表主键ID ORDER BY ROWNUM DESC) AS rn
  FROM 你的实际表名, SYNONYM_MAP
),
std_col2 AS (
  SELECT
    原始表主键ID,
    Column_2 AS original_val,
    REPLACE(Column_2, SHORT_FORM, LONG_FORM) AS std_val,
    ROW_NUMBER() OVER(PARTITION BY 原始表主键ID ORDER BY ROWNUM DESC) AS rn
  FROM 你的实际表名, SYNONYM_MAP
)
SELECT
  c1.original_val AS Column_1,
  c2.original_val AS Column_2,
  CASE WHEN c1.std_val = c2.std_val THEN 'Yes' ELSE 'No' END AS "Equal?"
FROM std_col1 c1
JOIN std_col2 c2 ON c1.原始表主键ID = c2.原始表主键ID
WHERE c1.rn = 1 AND c2.rn = 1;

效果验证 #

以上两种方案对示例数据的输出结果完全符合预期：

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