查询
SELECT COUNT(*), name, number
FROM tbl
GROUP BY name, number
HAVING COUNT(*) > 1
有时在检查重复项时,不区分大小写会导致漏检。
例如:sunny
和 Sunny
不会显示为重复项。
那么,在 PostgreSQL 中如何找到两列中所有可能的重复项呢?
SELECT COUNT(*), name, number
FROM tbl
GROUP BY name, number
HAVING COUNT(*) > 1
有时在检查重复项时,不区分大小写会导致漏检。
例如:sunny
和 Sunny
不会显示为重复项。
那么,在 PostgreSQL 中如何找到两列中所有可能的重复项呢?
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区分大小写。您可以通过将所有值转换为单个大小写来强制进行大小写不敏感的搜索:
SELECT COUNT(*), lower(name), number FROM TABLE
GROUP BY lower(name), number HAVING COUNT(*) > 1
ö
可能是一个“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)