postgresql 匹配多个字符串之一的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28938905/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query to match one of multiple strings
提问by Muhammad Taqi
I have following data in table:
我在表中有以下数据：
+----------------------+----------------------------------------------------------+--------------+
| subscriber_fields_id | name | field_type |
+----------------------+----------------------------------------------------------+--------------+
| 143 | Peshawar/Islamabad/Lahore/Swat/Mardan/Karachi | Job Location |
| 146 | Karachi | Job Location |
| 147 | Lahore and Karachi | Job Location |
| 149 | Karachi, Mirpur Khas, Sukkur, Layyah, Gilgit, Charsaddah | Job Location |
| 152 | Islamabad or Lahore | Job Location |
| 155 | Islamabad | Job Location |
| 157 | 7 Districts of Sindh and Karachi | Job Location |
+----------------------+----------------------------------------------------------+--------------+
My query is:
我的查询是：
select * from subscriberfields
where name like '%Khairpur,Islamabad,Karachi%';
Result:
结果：
+----------------------+-----------------------------------------------+--------------+
| subscriber_fields_id | name | field_type |
+----------------------+-----------------------------------------------+--------------+
| 143 | Peshawar/Islamabad/Lahore/Swat/Mardan/Karachi | Job Location |
| 152 | Islamabad or Lahore | Job Location |
| 155 | Islamabad | Job Location |
+----------------------+-----------------------------------------------+--------------+
It should return all rows where the name includes Islamabad, Khairpur or Karachi but it is not.
它应该返回名称中包含伊斯兰堡、凯尔布尔或卡拉奇的所有行，但事实并非如此。
回答by Erwin Brandstetter
For a proper solution, either normalize your database designor, barring that, consider full text search.
对于适当的解决方案，要么规范您的数据库设计，要么考虑全文搜索。
For a quick solution to the problem at hand, use a regular expression match (~)or three simple LIKEexpressions:
要快速解决手头的问题，请使用正则表达式匹配 ( ~)或三个简单LIKE表达式：
SELECT *
FROM subscriberfields
WHERE name ~ '(Khairpur|Islamabad|Karachi)';
Or:
或者：
...
WHERE (name LIKE '%Khairpur%' OR
name LIKE '%Islamabad%' OR
name LIKE '%Karachi%')
Or use ~*or ILIKEfor case-insensitive matching.
或者使用~*或ILIKE进行不区分大小写的匹配。
Since another answer suggested it: neveruse SIMILAR TO:
由于另一个答案建议它：永远不要使用SIMILAR TO：
回答by benscabbia
Try using SIMILAR TOlike below:
尝试使用SIMILAR TO如下：
SELECT * FROM subscriberfields
WHERE name SIMILAR TO '%(Khairpur|Islamabad|Karachi)%';
Also you should read up on database normalization. Your design could and should definitely be improved.
您还应该阅读数据库规范化。您的设计可以而且绝对应该改进。
回答by Mario Leonel
You can use this:
你可以使用这个：
select * from subscriberfields
where name like any(array['%Khairpur%','%Islamabad%','%Karachi%']);
https://postgres.cz/wiki/PostgreSQL_SQL_Tricks#LIKE_to_list_of_patterns
https://postgres.cz/wiki/PostgreSQL_SQL_Tricks#LIKE_to_list_of_patterns
回答by BabyDuck
Use OR in WHERE clause, like,
在 WHERE 子句中使用 OR，例如，
select * from subscriberfields where name like '%Khairpur%' OR name like '%Islamabad%' OR name like '%Karachi%';
Hope it works.
希望它有效。
