string - PostgreSQL : Find the string with the closest substring match

Question

如果我有一个表测试，其值如下:

id | value
----------------
1  | ABC 1-2-3
2  | AB 1-2-3-4-5
3  | ABC 1
4  | ABC 1-2
5  | ABC

我尝试输入的字符串是ABC 1-2-3-4-5，那么最接近的子字符串匹配(如果我可以这样称呼的话)应该是ABC 1-2-3。第 2 行不应该匹配，因为它没有“ABC”。如果输入字符串比实际记录短，我只能搜索字符串，但如果输入字符串更长则不能。例如

select * from test where value ilike 'ABC 1-2%';

但这也没有给我一个准确的记录，而只是那些以 ABC 1-2 开头的。我如何构建正确的 sql 语句来解决这个问题？

Answer 1

您可能对 pg_trgm extension 感兴趣:

create extension if not exists pg_trgm;

数据的标准相似性如下:

select *, similarity(value, 'ABC 1-2-3-4-5')
from test
order by 3 desc;

 id |    value     | similarity 
----+--------------+------------
  2 | AB 1-2-3-4-5 |        0.8
  1 | ABC 1-2-3    |   0.714286
  4 | ABC 1-2      |   0.571429
  3 | ABC 1        |   0.428571
  5 | ABC          |   0.285714
(5 rows)

但是，您始终可以在 WHERE 子句中添加其他条件:

select *, similarity(value, 'ABC 1-2-3-4-5')
from test
where value ilike 'abc%'
order by 3 desc;

 id |   value   | similarity 
----+-----------+------------
  1 | ABC 1-2-3 |   0.714286
  4 | ABC 1-2   |   0.571429
  3 | ABC 1     |   0.428571
  5 | ABC       |   0.285714
(4 rows)