Mysql - Select words from text

Question

Hello guys,

I have a table contains 300 locations 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 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")

but no chance!, can anyone help please?

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 does a completely different thing.

Answer 2

The only way to achieve it this way is to use a like %name% in your SQL - You can loop through your names to build the where clause if you need to and I would also suggest adding the stirng to a variable instead of repeating it like I have:

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

But doing it this way means that you could possibly bring back more results that you are expecting because it doesn't matter where it is in that string; even if is part of another word. You should be Ok if you are passing full country names through but passing something like united could being back United Kingdom and United States.

Answer 3

you can to try to use php to first query for the list of names you have in the database and then compare it with the text.

 // connect to the db first..

 // select the names from the db
 $sql = "SELECT names FROM countries";
 $result = mysql_query($sql) or die(mysql_error());

 // store names in an array
 $names = array();
 while($data = mysql_fetch_assoc($result)) $names[] .= $data;

then you can use PHP's array functions to extract the names from the text

 // store the text in an array per word
 $exploded_text = explode(' ',$text);

 // get the common strings (the names) from both arrays
 $extracted_names = array_intersect($exploded_text,$names);

 print_r($extracted_names);

i guess there are better and more efficient solutions to this but i hope you get the idea.

---

IN is used as a shortcut for multiple ORs, so that the following WHERE statement

WHERE (name = 'London' OR name = 'Paris' OR name = 'New York')

becomes

WHERE name IN ('London','Paris','New York')