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

回答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.

回答2:

It helps in my case:

WITH phrase_to_match AS (
  SELECT 'Super fast training' AS phrase
  UNION ALL
  SELECT '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