PostgreSQL中忽略特殊字符检索记录的实现方法

先看你的profile表数据：

要实现忽略逗号（或其他特殊字符）的模糊查询，核心思路就是把原始地址和查询条件都处理成去除特殊字符的形式，再做匹配。

方案1：只忽略逗号的查询 #

如果只需要处理逗号，直接用regexp_replace把地址和查询条件里的逗号全删掉就行：

SELECT * 
FROM profile 
WHERE regexp_replace(address, ',', '', 'g') ILIKE regexp_replace('7300 Overlook Ave Moncks Corner SC%', ',', '', 'g');

'g'参数是全局替换的意思，能把字符串里所有逗号都去掉。处理之后，原始地址里的逗号被清除，和你输入的不带逗号的查询条件就能匹配上。

方案2：忽略所有非字母/数字/空格的特殊字符（更通用） #

要是以后还遇到其他特殊字符（比如点、斜杠）需要忽略，可以直接替换掉所有不是字母、数字、空格的字符：

SELECT * 
FROM profile 
WHERE regexp_replace(address, '[^a-zA-Z0-9 ]', '', 'g') ILIKE regexp_replace('7300 Overlook Ave Moncks Corner SC%', '[^a-zA-Z0-9 ]', '', 'g');

正则[^a-zA-Z0-9 ]会匹配所有非字母、数字、空格的字符，替换为空后，两边的字符串就只剩有效内容，自然能匹配成功。

性能优化建议 #

如果表数据量比较大，上面的写法会让PostgreSQL没法用索引，查询速度会很慢。可以这么优化：

1. 加个生成列：提前把处理好的地址存成新列clean_address：

ALTER TABLE profile ADD COLUMN clean_address TEXT GENERATED ALWAYS AS (regexp_replace(address, '[^a-zA-Z0-9 ]', '', 'g')) STORED;

2. 给生成列建索引：

CREATE INDEX idx_profile_clean_address ON profile USING GIN (to_tsvector('english', clean_address));

3. 用全文检索查询：

SELECT * 
FROM profile 
WHERE to_tsvector('english', clean_address) @@ to_tsquery('english', '7300 & Overlook & Ave & Moncks & Corner & SC:*');

这么改之后，查询效率会提升很多，尤其适合大数据量的场景。

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