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

Answer 1

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

Answer 2

One of:

1. WHERE foo LIKE '%bar%'

2. WHERE foo ILIKE '%bar%' --case insensitive

3. WHERE foo ~* 'bar' --regex

4. WHERE foo ~* '\ybar\y' --matches bar but not foobar (\y is word boundary)

5. WHERE foo::tsvector @@ 'bar'::tsquery --matches bar but not foobar

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