Postgres Position function for whole phrases? String search

Question

Is there a way to use position or another function to find strings in a field but not have it be part of another word? For example if I am looking for the word "bench" with position I would do something like this

 POSITION('bench' IN products)

The products column would look something like this

 **Products**
 tool bench
 benchpress
 park bench
 benchmark
 bench heater
 utility bench tool

I only want to match the rows where bench is NOT part of another word (don't want to match benchpress or benchmark). Thank you

Trim the column to avoid before and after spaces . then write a case if there is a space then only check for existence (charindex function) else ignore. — Raj
– Raj, Commented May 13, 2020 at 6:56
Please use CTE (with clause) for next time to make responders concentrate on answer rather than formatting sample input. Thanks. — Tomáš Záluský
– Tomáš Záluský, Commented May 13, 2020 at 7:40

Richard Huxton · Accepted Answer · 2020-05-13 07:33:11Z

2

Use \y regex expression (word boundary matcher):

with t(word) as (values
  ('tool bench'),
  ('benchpress'),
  ('park bench'),
  ('benchmark'),
  ('bench heater'),
  ('utility bench tool')
)
select *
from t
where word ~ '\ybench\y'

Dbfiddle here.

edited May 13, 2020 at 7:33

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

answered May 13, 2020 at 7:11

Tomáš Záluský

12.3k4 gold badges44 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2024-06-28 14:29:11Z

Using the with clause from Tomáš Záluský you can simple use a like condition searching for is there a space before and after it, is there a space after it or is there a space before

with t(word) as (values
  ('tool bench'),
  ('benchpress'),
  ('park bench'),
  ('benchmark'),
  ('bench heater'),
  ('utility bench tool')
)
select *
from t
where word  like '% bench %' or word like 'bench %'  or word like  '% bench'

or you can split your string to an array, using space as delimiter and look, if there is a fitting element.

 with t(word) as (values
      ('tool bench'),
      ('benchpress'),
      ('park bench'),
      ('benchmark'),
      ('bench heater'),
      ('utility bench tool')
    )
    select * from t 
where string_to_array(word, ' ') @> array[ 'bench' ]

or you also can use postgres and its textsearch

with t(word) as (values
  ('tool bench'),
  ('benchpress'),
  ('park bench'),
  ('benchmark'),
  ('bench heater'),
  ('utility bench tool')
)
SELECT 
* from t 
where to_tsvector('english', word) @@ to_tsquery('english', 'bench')

So choose the right for your task, which fits best and is the fastest.

Collectives™ on Stack Overflow

Postgres Position function for whole phrases? String search

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related