在PostregSQL中列出匹配的记录

Question

我在搜索部分工作，比较输入值与存储的记录。具有值的数据库，如AB09CD1234。我有分解(用空格)输入字符串以匹配存储值，但当我给出像AB09 CD1234这样的输入时，它不会产生上述记录。

SELECT * FROM some_vehicle WHERE vehicle_number ILIKE E\'%AB09 CD 1234%' 

db：AB 09 C D 1234中的vehicle_number

输入字符串：AB09 CD 1234

我想结果有每一个可能的情况与输入字符串的组合。在PostgreSQL中有什么方法可以做到这一点吗？

“替换”功能能够消除字符串中的空格。在PostgreSQL中有什么方法可以消除所有特殊字符和空格吗？我尝试了不同的正则表达式，比如

regexp_replace(vehicle_number，'^a-z0-9\s'，'')和regexp_replace(vehicle_number，'(|-|)|\:|\@|#|\$|*|.|!|\，'，'')，但它不工作。

谢谢

经过几次尝试就弄明白了！已经非常接近了。"^a-zA-Z0-9“模式消除了包括空格在内的所有特殊字符，并将”g“选项作为PostgreSQL中regexp_replace的第四个参数。

Answer 1

您可以这样做，消除两个值中的所有空格：

SELECT * FROM some_vehicle
WHERE replace(lower(vehicle_number), ' ', '') =
      replace(lower('AB09 CD 1234'), ' ', '');

或者，如果您想保留子字符串匹配：

SELECT * FROM some_vehicle
WHERE replace(lower(vehicle_number), ' ', '') LIKE
      ('%' || replace(lower('AB09 CD 1234'), ' ', '') || '%');

请注意，这将无法使用您当前在vehicle_number上拥有的任何索引，并将导致seq扫描(如果幸运的话，也可能是索引扫描)，除非您创建一个专门的表达式索引来有效地处理此查询：

CREATE INDEX ON some_vehicle (( replace(lower(vehicle_number), ' ', '') ));

(使用LIKE的子字符串查询将是unable to use any index，因为模式以%开头。)

另一种方法是，如果您的vehicle_number值始终在完全相同的位置有空格，则将这些值存储在不带空格的位置，并将空格插入到显示层(或视图)中，因为您确切知道它们将位于何处。然后，您可以简单地将空格从任何搜索值中剔除。

Answer 2

如果要将索引与类似于provided by @cdhowie的查询一起使用(如果这是一个大表上的常见查询，则应该使用)，请使用附加模块提供的函数trigram索引

CREATE EXTENSION pg_trgm; -- once per database

CREATE INDEX some_name_idx ON some_vehicle
USING GIN (replace(vehicle_number, ' ', '')  gin_trgm_ops);

我没有使用lower()，因为这在你的问题中不是问题。匹配的查询将是：

SELECT * FROM some_vehicle
WHERE replace(vehicle_number, ' ', '')
      LIKE ('%' || replace('AB09 CD 1234', ' ', '') ||  '%');

关于SO的相关答案：

Effectively query on column that includes a substring

或者this one on dba.SE。

Answer 3

如果你是指空格的组合，那么：

SELECT * FROM some_vehicle WHERE vehicle_number ILIKE E\'%A%B%0%9%C%D%1%2%3%4%'