Postgres - WHERE clause on whether values contain a certain string

Question

If I want to retrieve all entries such that the column foo value contains a string 'bar', is there a simple way to do this in SQL, or Postgresql?

Something like ' WHERE foo = "bar"' but instead of = it would be something like ' WHERE foo CONTAINS "bar"'.

Postgres Version 9.3

John Hodge · Accepted Answer · 2015-05-21 16:07:54Z

43

Use the SQL LIKE statement. With that, you use a % as a wildcard. So, your WHERE statement could be something like:

WHERE foo LIKE '%bar%'

answered May 21, 2015 at 16:07

John Hodge

1,7151 gold badge15 silver badges13 bronze badges

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

Comments

Neil McGuigan · Accepted Answer · 2019-10-04 00:28:40Z

22

One of:

You can index foo to make LIKE, ILIKE, and regexp searches faster: http://www.postgresql.org/docs/9.3/static/pgtrgm.html#AEN154464 Or use a GIN or GIST index on a full-text column

answered May 21, 2015 at 17:58

Neil McGuigan

48.5k12 gold badges131 silver badges156 bronze badges