I'm developing a search application that takes a string and then break it down into a combination of two words and then search a table for matching patterns. An example:
'My cat ate a rat'
search patterns: 'my cat' 'cat ate' 'ate a' 'a rat'
At the same time, this should match with plurals and singulars of the combination. So 'my cat'
should match 'my cats'
and also "my cat's". Stop words should not be ignored and the two words must be in the same order with no words between them.
My question is, is tsvector the right tool for this or can this be done using the 'LIKE' operator alone? I'm asking this to decide whether I need to spend time diving deep into tsvector to build my queries.
Thanks!
note: this is my first time here, please let me know if this is not the right place to ask this type of question.
'my cat'
and like'cat ate'
. but at the same time the some variations like plural 'my cats' and 'cats ate' should yield a hit too as long as the two words are in the same order with no other word between them. – tito.300 Apr 9 at 15:15