Postgres LIKE with column value as substring

Question

I'm trying to compose a WHERE statement that will match rows where a column value is a substring of another string.

For example, I might have an event record with a name field of Edward Sharpe. I'd like to do something like:

SELECT * FROM events WHERE(name LIKE 'Edward Sharpe and the Magnetic Zeroes');

This doesn't work. I've also various permutations of:

SELECT * FROM events WHERE('%' || name || '%' LIKE 'Edward Sharpe and the Magnetic Zeroes');

Which also doesn't work.

Answer 1

Your second attempt is painfully close to correct. The LIKE keyword takes a string on its left, and a pattern on its right. Both can be expressions, but % only has a special meaning in the pattern to the right.

Try this:

 SELECT * FROM events
 WHERE name LIKE '%Edward Sharpe and the Magnetic Zeroes%';

Or rather this:

 SELECT * FROM events 
 WHERE 'Edward Sharpe and the Magnetic Zeroes' LIKE '%' || name || '%';

Also note that all string operations in Postgres are case sensitive by default. To match a pattern ignoring case, use ILIKE in place of LIKE.