sqlpostgresqlfull-text-searchpostgresql-9.1postgresql-9.0

ILIKE Match Word Boundaries PostgreSQL 9

When using the LIKE/ILIKE operator in PostgreSQL 9 is it possible to match word boundaries without having to use full blown ~ operator regular expressions?

For example

SELECT 'Super fast training' ILIKE '%train\M%' as match;

Where \M is the boundary at the end of a word and match returns false

Thanks,

Mark

Solution

  • you can do it with following trick:

    SELECT ' ' || 'Super fast training' ILIKE '%train %'

    but I don't hink so it is a good idea. You can use regular expression or PostgreSQL fulltext instead. PostgreSQL regular expressions are not significantly slower than ILIKE or LIKE.