SQL查询以匹配多个字符串之一

Question

我在表中有以下数据:

+----------------------+----------------------------------------------------------+--------------+
| 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 |
+----------------------+----------------------------------------------------------+--------------+

我的查询是:

select * from subscriberfields
where  name like '%Khairpur,Islamabad,Karachi%';

结果:

+----------------------+-----------------------------------------------+--------------+
| 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 |
+----------------------+-----------------------------------------------+--------------+

它应该返回名称包含 Islamabad、Khairpur 或 Karachi 但实际上没有的所有行。

Answer 1

要获得合适的解决方案，normalize your database design或者，除此之外，请考虑 full text search .

要快速解决手头的问题，请使用 regular expression match (~)或三个简单的 LIKE表达式:

SELECT *
FROM   subscriberfields 
WHERE  name ~ '(Khairpur|Islamabad|Karachi)';

或者:

...
WHERE (name LIKE '%Khairpur%'
    OR name LIKE '%Islamabad%'
    OR name LIKE '%Karachi%')

或者使用~*或者ILIKE进行不区分大小写的匹配。

因为另一个答案建议它:从不使用SIMILAR TO:

• Using SIMILAR TO for a regex?
• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL