Mysql - Select words from specified text

Question

Hello guys,

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

for example:

" Transport 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 ?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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