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 'Supe

Solution 1:

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.

Solution 2:

It helps in my case:

WITH phrase_to_match AS (
  SELECT'Super fast training'AS phrase
  UNIONALLSELECT'Super fast train'AS phrase
)

SELECT' '|| phrase ||' ' ILIKE '% train %'FROM phrase_to_match

Spaces in begin and end of phrase and match phrase are necessary. It didn't work correctly without spaces.

This example works faster than when we use ~* operator.

P.S.: Thank's to https://stackoverflow.com/a/29798772/2997850