CODEWITHSUNDEEP
phptextmysqlsql-like
aniss.bouraba
aniss.bouraba

Reputation: 453

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:

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

Upvotes: 1

Views: 735

Answers (4)

ITroubs
ITroubs

Reputation: 11215

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

Upvotes: 1

Raul Lapeira Herrero
Raul Lapeira Herrero

Reputation: 987

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.

Upvotes: 0

BarsMonster
BarsMonster

Reputation: 6585

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.

Upvotes: 3

MatTheCat
MatTheCat

Reputation: 18761

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

Upvotes: 0

Related Questions