Searching individual words in a string

Question

I know about full-text search, but that only matches your query against individual words. I want to select strings that contain a word that starts with words in my query. For example, if I search:

appl

the following should match:

a really nice application
apples are cool
appliances

since all those strings contains words that start with appl. In addition, it would be nice if I could select the number of words that match, and sort based on that.

How can I implement this in PostgreSQL?

Answer 1

Prefix matching with Full Text Search

FTS supports prefix matching. Your query works like this:

SELECT * FROM tbl
WHERE  to_tsvector('simple', string) @@ to_tsquery('simple', 'appl:*');

Note the appended :* in the tsquery. This can use an index. See:

• Get partial match from GIN indexed TSVECTOR column

Alternative with regular expressions

SELECT * FROM tbl
WHERE  string ~ '\mappl';

Quoting the manual here:

\m .. matches only at the beginning of a word

To order by the count of matches, you could use regexp_matches()

SELECT tbl_id, count(*) AS matches
FROM  (
   SELECT tbl_id, regexp_matches(string, '\mappl', 'g')
   FROM   tbl
   WHERE  string ~ '\mappl'
   ) sub
GROUP  BY tbl_id
ORDER  BY matches DESC;

Or regexp_split_to_table():

SELECT tbl_id, string, count(*) - 1 AS matches
FROM  (
   SELECT tbl_id, string, regexp_split_to_table(string, '\mappl')
   FROM   tbl
   WHERE  string ~ '\mappl'
   ) sub
GROUP  BY 1, 2
ORDER  BY 3 DESC, 2, 1;

db<>fiddle here
_{Old sqlfiddle}

Postgres 9.3 or later has index support for simple regular expressions with a trigram GIN or GiST index. The release notes for Postgres 9.3:

Add support for indexing of regular-expression searches in pg_trgm (Alexander Korotkov)

See:

• PostgreSQL LIKE query performance variations

Depesz wrote a blog about index support for regular expressions.

Answer 2

SELECT * FROM some_table WHERE some_field LIKE 'appl%' OR some_field LIKE '% appl%';

As for counting the number of words that match, I believe that would be too expensive to do dynamically in postgres (though maybe someone else knows better). One way you could do it is by writing a function that counts occurrences in a string, and then add ORDER BY myFunction('appl', some_field). Again though, this method is VERY expensive (i.e. slow) and not recommended.

For things like that, you should probably use a separate/complimentary full-text search engine like Sphinx Search (google it), which is specialized for that sort of thing.

An alternative to that, is to have another table that contains keywords and the number of occurrences of those keywords in each string. This means you need to store each phrase you have (e.g. really really nice application) and also store the keywords in another table (i.e. really, 2, nice, 1, application, 1) and link that keyword table to your full-phrase table. This means that you would have to break up strings into keywords as they are entered into your database and store them in two places. This is a typical space vs speed trade-off.