在两列中查找可能的重复项，忽略大小写和特殊字符

Question

SELECT COUNT(*), name, number
FROM   tbl
GROUP  BY name, number
HAVING COUNT(*) > 1

它有时无法在小写和大写之间找到重复项。
例如：sunny和Sunny不显示为重复项。
那么如何在PostgreSQL中为两列找到所有可能的重复项。

Answer 1

（海报澄清后的最新答案）：不入耳或脱衣口音（dicratics）的想法通常是假的。如果你正在匹配数据，以找出一些被误导的用户或应用程序是否将简历咀嚼成简历，这没关系，但把一个改成另一个是完全错误的，因为它们是不同的词。即使这样，它也只是一种工作，应该与字符串相似性匹配系统相结合，如三角形或列文斯坦距离。

“非重音字符”的概念假定任何重音字符都有一个有效的等效非重音字符，或者至少任何给定的重音字符都被替换为单词ascii化表示中最多一个非重音字符。这根本不是真的；在一种语言中，可能是一个“u”音，而在另一种语言中，可能是一个长的“oo”，而“ascii化”的拼写约定可能反映了这一点。因此，在语言中，虚构单词“Tapö”的正确“非重音”可能是“Tapu”，而在另一种语言中，这个虚构的单词可能被ascii码化为“Tapoo”。在这两种情况下，“Tapo”的“无重音”形式都与人们在强制输入ascii字符集时实际写入的内容不匹配。带有dicratics的单词也可以ascii码化为连字符单词。

您可以在英语中看到连字，其中单词dæmon是ascii化的守护进程。如果你去掉连字，你会得到与daemon不匹配的dmon，daemon是一种常见的拼写。这同样适用于以太，它通常被ascii码化为以太或以太。您也可以在德语中看到这一点，它带有ß，通常“扩展”为ss。

可以使用字符类正则表达式除去除指定字符集以外的所有字符。在这种情况下，我们使用escape（根据手册中字符类的缩写）排除“符号”，但不排除重音字符：

regress=# SELECT regexp_replace(lower(x),'\W','','g') 
          FROM ( VALUES ('$s^o&f!t'),('Café') ) vals(x);
 regexp_replace 
----------------
 soft
 café
(2 rows)

如果您也想过滤掉重音字符，您可以定义自己的字符类：

regress=# SELECT regexp_replace(lower(x),'[^a-z0-9]','','g')
          FROM ( VALUES ('$s^o&f!t'),('Café') ) vals(x);
 regexp_replace 
----------------
 soft
 caf
(2 rows)

如果您确实打算用一些重音字符替换类似的非重音字符，您可以根据这篇wiki文章使用翻译：

regress=# SELECT translate(
        lower(x),
        'âãäåāăąÁÂÃÄÅĀĂĄèééêëēĕėęěĒĔĖĘĚìíîïìĩīĭÌÍÎÏÌĨĪĬóôõöōŏőÒÓÔÕÖŌŎŐùúûüũūŭůÙÚÛÜŨŪŬŮ',
        'aaaaaaaaaaaaaaaeeeeeeeeeeeeeeeiiiiiiiiiiiiiiiiooooooooooooooouuuuuuuuuuuuuuuu'
    )
    FROM ( VALUES ('$s^o&f!t'),('Café') ) vals(x);

 translate 
-----------
 $s^o&f!t
 cafe
(2 rows)

Answer 2

PostgreSQL默认区分大小写。您可以通过将所有值转换为单个大小写来强制它在搜索期间不区分大小写：

SELECT COUNT(*), lower(name), number FROM TABLE 
GROUP BY lower(name), number HAVING COUNT(*) > 1

• 注：这尚未在Postgres中进行测试

Answer 3

使用其中一个将字符折叠为小写或大写。特殊字符不受影响：

SELECT count(*), lower(name), number
FROM   tbl
GROUP  BY lower(name), number
HAVING count(*) > 1;

如果您真的想忽略变音符号，就像您的评论所暗示的那样，请安装附加模块unaccent，它提供了一个删除重音的文本搜索字典以及通用函数unaccent（）：

CREATE EXTENSION unaccent;

非常简单：

SELECT lower(unaccent('Büßercafé'));

结果：

busercafe

这不会删除非字母。添加regexp_replace（），如@Craig为此提到的：

SELECT lower(unaccent(regexp_replace('$s^o&f!t Büßercafé', '\W', '', 'g') ));

结果：

softbusercafe

您甚至可以在此基础上构建功能索引：

• PostgreSQL是否支持“不区分重音”的排序规则