Mysql - Select words from specified text

I have a table contains 413,000 places name (Pris, London,...) is there a way (query) to select locations from specified text.

for example:

" Trans开发者_如何学编程port for London (TfL) is in talks with its American, Australian and European partners about issuing a single contactless card for Paris, New York".

I want a query to get:

* London
* Paris
* New York

I tried

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

it's slow and don't give the exact word like (Paris, New York, Euro, York ...)

How can I get exact the exact word ?

You'd better split up your sentence into words on your front/middle-end code, and do something like

SELECT name from countries where 
name in ('Transport', 'for', 'London')

e.t.c This way it will (try to) use indexes (if any) for name.

What you need is the mysql fulltext search. This should work:

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

It's weird to have such query to perform, can't you change your table's structure to match simpler data ?

Do it the opposite way:

1. Extract the rearched strings from the raw text
2. Make a simple SQL.

select * from countries where text like '%place1%' or like '%place2%'...

Build the SQL with a for or while, just one of several feasible options.