2

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

Mark
  • 402
  • 1
  • 3
  • 11

2 Answers2

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.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
1

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

Stepan Zakharov
  • 420
  • 6
  • 11