Searching a super string in a list of strings - POSTGRES

Question

I am not sure if I have worded the question correctly. But how can we search a string in POSTGRES such that the following results can be achieved.

String to search:

Google Pvt Ltd

Data in table

symbol, company name
GOOG, Google Ltd
FACEBOOK, Facebook Corp
APPLE, Apple Inc
DELL, Dell Ltd

How do I return search result

GOOG,Google Ltd

, logic being it returns results based on the maximum words matched.

I am looking into the full text search option in POSTGRES and I can understand the tokenization using to_tsvector. But I'm not sure how to proceed after this. Is this type of searching possible?

klin · Accepted Answer · 2018-06-17 13:32:47Z

2

You can use the pg_trgm extension.

create extension if not exists pg_trgm;

with my_table(symbol, company_name) as (
values
    ('GOOG', 'Google Ltd'),
    ('FACEBOOK', 'Facebook Corp'),
    ('APPLE', 'Apple Inc'),
    ('DELL', 'Dell Ltd')
)

select *, similarity(company_name, 'Google Pvt Ltd')
from my_table
order by similarity desc;

  symbol  | company_name  | similarity 
----------+---------------+------------
 GOOG     | Google Ltd    |   0.733333
 DELL     | Dell Ltd      |        0.2
 APPLE    | Apple Inc     |  0.0416667
 FACEBOOK | Facebook Corp |          0
(4 rows)

You can define the current similarity threshold and simply use the % operator, e.g.:

select set_limit(0.6);

select *
from my_table
where company_name % 'Google Pvt Ltd'

 symbol | company_name 
--------+--------------
 GOOG   | Google Ltd
(1 row)

answered Jun 17, 2018 at 13:32

klin

123k15 gold badges240 silver badges262 bronze badges

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

4 Comments

AshTyson Over a year ago

This fits my requirement. Thanks!

Gordon Linoff Over a year ago

@Sammy . . . This is -- no doubt -- the best solution to your problem. But your actual question is "the maximum words matched", and this doesn't do maximum words; it measures similarity.

AshTyson Over a year ago

Conceptually I feel its the same. Maybe I didn't word it properly. But it gives me the results. But can we set the limit per query instead of globally.

klin Over a year ago

You can use the function and operator interchangeably, e.g. where similarity(company_name, 'Google Pvt Ltd') > 0.6 instead of where company_name % 'Google Pvt Ltd'

Gordon Linoff · Accepted Answer · 2018-06-17 13:03:33Z

1

I am not sure if you need full text search for this -- that depends on performance. There are other methods, such as breaking the columns and input into words and matches directly on them.

Here is one approach that uses regexp_matches():

select v.*,
       (select count(*) from regexp_matches(symbol || ' ' || company, replace('Google Pvt Ltd', ' ', '|'), 'g')) as matches
from (values ('GOOG', 'Google Ltd'),
             ('FACEBOOK', 'Facebook Corp'),
             ('APPLE', 'Apple Inc'),
             ('DELL', 'Dell Ltd')
    ) v(symbol, company)
order by matches desc
fetch first 1 row only;

answered Jun 17, 2018 at 13:03

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

AshTyson Over a year ago

This works but in a couple of cases it gives me no results or unexpected ones.

Collectives™ on Stack Overflow

Searching a super string in a list of strings - POSTGRES

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related