CODEWITHSUNDEEP
sqlpostgresql
thelok
thelok

Reputation: 83

Using Table As Input to Query for Substrings

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.

Upvotes: 1

Views: 25

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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 || '%';

Upvotes: 2

Related Questions