21

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.

amd
  • 512
  • 1
  • 5
  • 13
  • Have you considered [regexp_matches](http://www.postgresql.org/docs/9.3/static/functions-string.html)? – mlt Jun 26 '14 at 22:44
  • Is it `select 'Edward Sharpe and the Magnetic Zeroes' like '%' || name || '%' from (select 'Edward Sharpe'::text "name") foo`? – mlt Jun 26 '14 at 22:51
  • @mlt No, that query makes no sense. As you've written it there, it will return a single unnamed column with the value `true`, if I'm not mistaken. – IMSoP Jun 26 '14 at 23:09
  • 1
    @mlt But what's with the extra sub-query, and not mentioning any actual table? It's a valid query, but not very similar to what was asked for. – IMSoP Jun 26 '14 at 23:17

1 Answers1

33

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
IMSoP
  • 89,526
  • 13
  • 117
  • 169
  • Your second example did it. Apparently the wildcard-ed value has to come after the LIKE statement. Thank you!! – amd Jun 26 '14 at 22:54
  • I just tweaked my answer because WHERE doesn't need any brackets for a simple case like this. They won't hurt, just a waste of keystrokes. – IMSoP Jun 26 '14 at 22:57
  • Some typos. And the first query is off target AFAICT. – Erwin Brandstetter Jun 26 '14 at 23:03
  • @ErwinBrandstetter Feel free to edit for typos, I'm on a mobile, so positioning accurately is tricky. I gave both queries to stress that the %s can go onto either value, as long as they're on the right of the LIKE – IMSoP Jun 26 '14 at 23:06
  • @Erwin Oh, I'm amused you edited to say LIKE is an operator: I wrote that initially, but wasn't sure, because operators have such a specific meaning in Postgres... :) – IMSoP Jun 26 '14 at 23:12
  • 1
    Well, strictly speaking you are right. The actual *operator* behind the scenes is `~~` or `~~*` for `ILIKE`. So I switched back to "keyword". Careful, btw, the otherwise equivalent operator `~~` is ranked differently in operator precedence. You would have to use parentheses: `... ~~ ('%' || name || '%')`. – Erwin Brandstetter Jun 26 '14 at 23:15