current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite

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.

share|improve this question
    
Have you considered regexp_matches? – 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
up vote 4 down vote accepted

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.

share|improve this answer
    
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
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.