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.

Is it select 'Edward Sharpe and the Magnetic Zeroes' like '%' || name || '%' from (select 'Edward Sharpe'::text "name") foo? — mlt
– mlt, Commented Jun 26, 2014 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
– IMSoP, Commented Jun 26, 2014 at 23:09
@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
– IMSoP, Commented Jun 26, 2014 at 23:17

Erwin Brandstetter · Accepted Answer · 2014-06-26 23:09:29Z

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.

edited Jun 26, 2014 at 23:09

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

answered Jun 26, 2014 at 22:45

IMSoP

99.7k18 gold badges135 silver badges183 bronze badges

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

6 Comments

amd Over a year ago

Your second example did it. Apparently the wildcard-ed value has to come after the LIKE statement. Thank you!!

IMSoP Over a year ago

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.

Erwin Brandstetter Over a year ago

Some typos. And the first query is off target AFAICT.

IMSoP Over a year ago

@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

Erwin Brandstetter Over a year ago

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

|

Collectives™ on Stack Overflow

Postgres LIKE with column value as substring

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related