How to match a superstring in PostgreSQL?

Question

I have a column of words whose elements look like this:

id | word
---+---------
1  | cute
2  | advanced
3  | unusual

Suppose I want to run a query for the words "cuter" or "cutest." I'd like to do something like:

SELECT * FROM words WHERE word% LIKE 'cutest'

Of course, I realize that's not valid SQL syntax, but I want to match a superstring, not a substring.

I don't want to use the similarity package because it will match things like "notcute", whereas I only want to specify that the wildcard should go at the end of the field.

Is this possible to achieve in Postgres?

Answer 1

The pattern goes to the right of LIKE. So:

SELECT * FROM words
WHERE 'cutest' LIKE word || '%'
AND    word Like 'cute%';

I added another WHERE condition to eliminate false positives on words like 'cut' or just 'c'. Also, this can (and typically will) still use an index, while the expression 'cutest' LIKE word || '%' absolutely cannot.

Note that 'cuter' doesn't qualify. Your question is a bit inconsistent there.

See:

• PostgreSQL LIKE query performance variations

To treat special characters in your words as non-special, you'll have to escape accordingly. See:

• Escape function for regular expression or LIKE patterns