SQL如何筛选匹配去除特殊符号后值相同的字符串数据

实现思路 #

核心是通过正则替换清除所有非字母、数字的特殊字符得到标准化字符串，再通过自关联匹配标准化后值相同但原始值不同的行，同时过滤无匹配项和重复配对的结果，确保输出符合列定义要求。

不同数据库参考语句 #

替换语句中的your_table_name为你的实际表名即可使用。

MySQL 8.0+

SELECT 
  ROW_NUMBER() OVER (ORDER BY t1.id) AS id,
  t1.column1 AS column1,
  t2.column1 AS column2
FROM 
  your_table_name t1
  JOIN your_table_name t2 
    ON REGEXP_REPLACE(t1.column1, '[^a-zA-Z0-9]', '') = REGEXP_REPLACE(t2.column1, '[^a-zA-Z0-9]', '')
WHERE 
  t1.column1 <> t2.column1
  AND t1.id < t2.id
  -- 保证column1为纯字母数字的无特殊符号值，column2为带特殊符号的原值
  AND t1.column1 NOT REGEXP '[^a-zA-Z0-9]'
  AND t2.column1 REGEXP '[^a-zA-Z0-9]'
ORDER BY t1.id;

PostgreSQL

SELECT 
  ROW_NUMBER() OVER (ORDER BY t1.id) AS id,
  t1.column1 AS column1,
  t2.column1 AS column2
FROM 
  your_table_name t1
  JOIN your_table_name t2 
    ON REGEXP_REPLACE(t1.column1, '[^a-zA-Z0-9]', '', 'g') = REGEXP_REPLACE(t2.column1, '[^a-zA-Z0-9]', '', 'g')
WHERE 
  t1.column1 <> t2.column1
  AND t1.id < t2.id
  AND t1.column1 !~ '[^a-zA-Z0-9]'
  AND t2.column1 ~ '[^a-zA-Z0-9]'
ORDER BY t1.id;

SQL Server 2017+

SELECT 
  ROW_NUMBER() OVER (ORDER BY t1.id) AS id,
  t1.column1 AS column1,
  t2.column1 AS column2
FROM 
  your_table_name t1
  JOIN your_table_name t2 
    ON REGEXP_REPLACE(t1.column1, '[^a-zA-Z0-9]', '') = REGEXP_REPLACE(t2.column1, '[^a-zA-Z0-9]', '')
WHERE 
  t1.column1 <> t2.column1
  AND t1.id < t2.id
  AND t1.column1 NOT LIKE '%[^a-zA-Z0-9]%'
  AND t2.column1 LIKE '%[^a-zA-Z0-9]%'
ORDER BY t1.id;

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