Postgresql : Find the string with the closest substring match

Question

If I have a table test with values like :

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

and the input string I'm trying to is ABC 1-2-3-4-5, then the closest substring match (if I could call it that) should be ABC 1-2-3. Row # 2 should not match because it doesn't have the "ABC". I've only been able to search for the string if the input string is shorter than the actual records, but not if it's longer. e.g

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

but this also does not give me an exact record, but only those starting with ABC 1-2. How do I construct the proper sql statement to solve this?

klin · Accepted Answer · 2017-02-23 01:40:43Z

7

You may be interested in pg_trgm extension:

create extension if not exists pg_trgm;

Standard similarities for your data are as follows:

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)

However you can always add additional criteria in WHERE clause:

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)

answered Feb 23, 2017 at 1:40

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

muffin Over a year ago

I upvoted this because there's an actual measure of similarity and i can probably use this in the future.

Bohemian · Accepted Answer · 2017-02-23 02:23:53Z

3

Reverse the comparison:

select * from test
where 'ABC 1-2-3-4-5' ilike value || '%'
order by length(value) desc

The best (ie longest) matches will be returned first.

answered Feb 23, 2017 at 2:23

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

4 Comments

Vikas Over a year ago

It works like magic. How? Will it work similarly on MySQL/Oracle/MSSQL as well?

Bohemian Over a year ago

@Vikas the "magic" is reversing the usual condition of where my_column like some_value to where some_value like my_column%, which in words means "where some_value starts with my_column". Yes, this will work for all databases (but change ilike to like - ilike is Postgres only).

Vikas Over a year ago

Have you written more on this topic? I would like learn more to create a solution that can stand the test of time. (various combinations of conditions including multiple ILIKE conditions)

Bohemian Over a year ago

@Vikas no, this is all I have written about it

Collectives™ on Stack Overflow

Postgresql : Find the string with the closest substring match

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related