Mysql -从文本中选择单词

Question

我有一张包含300个地点的桌子(Pris，伦敦，.)是否有方法(查询)从指定的文本中选择位置。

例如：

伦敦交通运输公司(TfL)正与其美国、澳大利亚和欧洲合作伙伴就向纽约巴黎发放一张非接触式卡进行谈判。

我想要一个查询：

• London
• Paris
• New York

我试过：SELECT name FROM coutries WHERE name IN ("Transport for London (TfL) is in talks with its American, Australian and European partners about issuing a single contactless card for Paris, New York")

但没有机会！有人能帮忙吗？

Answer 1

SELECT name 
FROM coutries 
WHERE ("Transport for London (TfL) is in talks with its American, Australian and European partners about issuing a single contactless card for Paris, New York") LIKE CONCAT('%', name, '%');

IN做的事情完全不同。

Answer 2

实现这种方法的唯一方法是在SQL中使用like %name% --如果需要，可以循环遍历您的名称来构建where子句，我还建议将搅拌添加到变量中，而不是像我一样重复它：

SELECT name FROM countries WHERE ('Transport for London (TfL) is in talks with its American, Australian and European partners about issuing a single contactless card for Paris, New York') like '%London%' or ('Transport for London (TfL) is in talks with its American, Australian and European partners about issuing a single contactless card for Paris, New York') like '%Paris%' or ('Transport for London (TfL) is in talks with its American, Australian and European partners about issuing a single contactless card for Paris, New York') like '%New York%';

但是，这样做意味着您可能会带来更多您所期望的结果，因为它在字符串中的位置并不重要，即使它是另一个单词的一部分。如果您正在传递完整的国家名称，那么您应该不会介意，但是传递类似united之类的东西可能会返回United Kingdom和United States。