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

Question

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

sqlpostgresqlduplicatespattern-matchingcase-insensitive

8

查询

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

有时在检查重复项时，不区分大小写会导致漏检。
例如：sunny 和 Sunny 不会显示为重复项。
那么，在 PostgreSQL 中如何找到两列中所有可能的重复项呢？

- Ghostman

当你说“特殊字符”时，你是指你希望“Soft”和“$s^o&f!t”相等吗？还是你指的是重音符号字符，你希望“Cafe”和“Café”被视为相等？ - Craig Ringer

@CraigRinger 是的!!! 这也是一种可能性。 - Ghostman

你是指哪一个？还是说“两者都是”？ - Craig Ringer

"Soft" 和 "$s^o&f!t" - Ghostman

3个回答

4

默认情况下，PostgreSQL区分大小写。您可以通过将所有值转换为单个大小写来强制进行大小写不敏感的搜索：

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

注意：这在Postgres中尚未经过测试。

- Palpatim

1

（在发布者澄清后更新的答案）“去重音”或剥离重音符号（即变音符号）的想法通常是无意义的。如果您正在匹配数据以查明某些误导用户或应用程序将“résumé”混淆为“resume”，那么这还算可以，但将一个词更改为另一个词是完全错误的，因为它们是不同的单词。即使这样做，它也只能部分起作用，并且应与字符串相似度匹配系统（如trigrams或Levenshtein distances）结合使用。

“去重音”这个概念假设任何带重音的字符都有一个单一有效的等价无重音字符，或者至少任何给定的带重音字符在单词的ASCII表示中被替换为最多一个无重音字符。但这并不是真的；在某种语言中，ö可能是一个“u”音，而在另一种语言中，它可能是一个长的“oo”音，而“ASCII-化”的拼写约定可能反映了这一点。因此，在某种语言中，虚构的假词“Tapö”的正确“去重音”可能是“Tapu”，而在另一种语言中，这个想象中的单词可能被ASCII化为“Tapoo”。在任何情况下，“Tapo”的“去重音”形式都不会与人们在强制使用ASCII字符集时实际书写的形式相匹配。带有二分音符的单词也可以被ASCII化为连字符单词。

您可以使用连字号在英语中查看此内容，其中单词dæmon被ASCII化为daemon。如果您去掉了连字号，您将得到dmon，这与常见的拼写daemon不匹配。同样适用于æther，通常被ASCII化为aether或ether。您还可以在德语中看到这一点，例如ß，通常被“扩展”为ss。

如果您必须尝试“去重音”，“规范化”重音或“去除”重音：

您可以使用字符类正则表达式来除去指定集合以外的所有字符。在这种情况下，我们使用\W转义符（代表字符类[^[:alnum:]_]如手册所述）来排除“符号”，但不包括重音字符：

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)

如果您实际上想要用一些没有重音的字符替换一些带重音的字符，您可以按照这篇维基文章中所述使用translate：

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

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

- Craig Ringer

我使用了 PHP 正则表达式并将其存储到一个变量中，然后完成了它！这是一个很长的过程！不管怎样，谢谢啦伙计... 我会接受你的答案！ - Ghostman

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

`lower()`/ `upper()`

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

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

`unaccent()`

如果你想忽略变音符号，就像你的评论所暗示的那样，请安装额外的模块unaccent，它提供了一个文本搜索词典，可以去除重音，并且还提供了通用函数unaccent()：

CREATE EXTENSION unaccent;

使其非常简单：

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

结果：

busercafe

这段代码不能去除非字母字符。像@Craig提到的那样，加入regexp_replace()来解决这个问题：

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

结果：

softbusercafe

你甚至可以在此基础上建立一个功能性索引：

PostgreSQL是否支持"重音不敏感"的排序规则？

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

查询

lower()/ upper()

unaccent()

如果您必须尝试“去重音”，“规范化”重音或“去除”重音：

`lower()`/ `upper()`

`unaccent()`