Postgres LIKE 以列值作为子字符串 [英] Postgres LIKE with column value as substring

问题描述

我正在尝试编写一个 WHERE 语句，该语句将匹配列值是另一个字符串的子字符串的行.

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

例如，我可能有一个 event 记录，其中 name 字段为 Edward Sharpe.我想做这样的事情:

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');

这也不起作用.

推荐答案

您的第二次尝试非常接近正确.LIKE 关键字在其左侧接受一个 string，在其右侧接受一个 pattern.两者都可以是表达式，但 % 只在右边的模式中有特殊含义.

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.

试试这个:

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

或者更确切地说:

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

还要注意，Postgres 中的所有字符串操作默认都区分大小写.要匹配忽略大小写的模式，请使用 ILIKE 代替 LIKE.

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.

这篇关于Postgres LIKE 以列值作为子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！