Compare string against MySQL column

Question

Say I have the following scenario:

$string1 = 'This is my string with the city New York in';
$string2 = 'This is my string with the city Los Angeles in';

MySQL-DB:

id|city
 0|San Diego
 1|Memphis
 2|Los Angeles
 3|Budapest
 4|New York

How should I do to take the whole strings - without splitting it - and check if any of the values in city is occurring in any of the strings?

I got lots of string with more info in than stated above.

i have several ideas, what have *you* tried? – Jun 19 '13 at 20:38 — , Jun 19 '13 at 20:38

score 1 · Answer 1 · answered Jun 19 '13 at 20:51

If you are doing it in PHP.

One way would be to query the cities into an array. Then iterate through the array using strpos to see if the cities are contained in your string e.g.

foreach($city in arrayOfCities)
{
  if(strpos($string1, $city) !== false)
  {
    //city found in string do something
  }
}

This may not be the exact code but strpos will work: http://www.php.net/manual/en/function.strpos.php

score 1 · Accepted Answer · answered Jun 19 '13 at 20:58

In MySQL you can do something like this:

SELECT id, city
FROM myTable
WHERE INSTR('This is my string with the city New York in', city) > 0

Something like this will find the names even if they're part of a word, so it will find Nome in "The nomenclature of Sao Paulo". Bad example, but the best I could think of: Nome matches because it's found in nomenclature. To avoid these types of matches a regular expression is needed:

SELECT id, city
FROM myTable
WHERE 'This is my string with the city New York in'
      RLIKE CONCAT('(^|[^[:alpha:]])', city, '($|[^[:alpha:]])')

The regex (after concatenation) would read, for example:

(^|[^[:alpha:]])New York($|[^[:alpha:]])

... which means: Match the beginning of the string or a non-letter character, then the value "New York", then the end of the string or a non-letter character. In short, it will only find whole words.

Thanks for answer, sorry for late reply. I get this error message _Unknown column 'New York' in 'where clause' SELECT `city_name` FROM (`city`) WHERE `New York` RLIKE CONCAT('(^|[^[:alpha:]])', city_name, '($|[^[:alpha:]])')_ my SQL-question looks like this (Coodeigniter): ` $this->db->select('city_name'); $where = "$string RLIKE CONCAT('(^|[^[:alpha:]])', city_name, '($|[^[:alpha:]])')"; $this->db->where($where); $query = $this->db->get('city'); ` What am I doing wrong? — JohnF, Jun 25 '13 at 20:57

score 1 · Answer 3 · answered Jun 19 '13 at 20:58

1

WHERE $str1 LIKE CONCAT('%', city, '%') OR $str2 LIKE CONCAT(...) ...

ps: keep in mind $str1 should be properly escaped or used as a prepared statement parameter.

answered Jun 19 '13 at 20:58

zerkms

249,484
69
436
539

score 0 · Answer 4 · answered Jun 19 '13 at 20:42

0

You can use regular expressions in both php and mysql.

For mySQL you can use the REGEXP operator

In php you can use preg_match

The regex would be

".*" + city + ".*"

answered Jun 19 '13 at 20:42

Nick Humrich

14,905
8
62
85

score 0 · Answer 5 · answered Jun 19 '13 at 20:42

i would try 2 things and see which is the fasets\ works best based on what eve else im doing

get all the cities implode() in to a pipe (|) separated list and use that in a preg match which i loop against the strings.
feed the cities from a db in a while loop and use strpos() to check for there existence in the string.

Compare string against MySQL column

5 Answers5