Using Table As Input to Query for Substrings

Question

I've got a LARGE table of words in PostgreSQL that have an operation mapped to a number that it should perform if the word matches. For example:

  words_table
words | operation
-----------------
fox   |    1
brown |    2
cow   |    1
moo   |    3
...

I want to be able to provide a string and see what operations in the table are a substring of the input, ignoring case. For example, both the input strings:

How brown are your cows and foxes?
Howbrownareyourcowsandfoxes?

Would return 1, 2. This is an unusual problem for me because I want to use the table of words as the "input" of the data. I realize that this operation will result in a full table scan and that I would need to do a union/distinct of the operations, but I have no idea how to start my query/subquery (I'd imagine I have to use a CONTAINS/LIKE subquery somewhere) to have the table be the input to check for substrings.

Any assistance formulating the SQL statement or strategies would be appreciated.

Gordon Linoff · Accepted Answer · 2015-04-22 22:42:11Z

2

Because you are not dealing with words (delimited character strings), full text indexing is not of use. You might be able to make some progress with ngrams.

The basic query is

select distinct wt.operation
from words_table wt
where YOURSTRING like '%' || wt.words || '%';

answered Apr 22, 2015 at 22:42

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

thelok Over a year ago

Awesome! Thanks very much, the query appears much simpler than expected.

Collectives™ on Stack Overflow

Using Table As Input to Query for Substrings

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related