Mysql WHERE column is in string

Question

I hope, this is not a very stupid question, but since some hours, I'm looking for a solution, but without success.

I have a table with name of cities:

cities.name

Berlin
New York
Hamburg
...

And I have a string: "Bonn New York Chicago"

Now, I would like to pick all "known" cities from this string. But as you can see, I'm not able to split this string to an array (because of "New York"). So "FIND_IN_SET" is not suitable for me. The function "LIKE" is working in the "wrong direction".

So I would need something like %cities.name% EKIL $string :-D

Does anyone have an idea, how I can handle this problem without using PHP?

I hope, I'm not missing the forest for the trees...

Thanks a lot for your help!

Tobias

This is as much a MySQL problem as a PHP one. Use WHERE name IN ('Berlin', 'New York', 'Hamburg') — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 30, 2016 at 9:51
@TimBiegeleisen I think the author does not know yet that the haystack string contains 'Berlin', 'New York' instead of 'Berlin New', 'York Hamburg', etc. — NoChecksum
– NoChecksum, Commented Jan 30, 2016 at 9:55
@NoChecksum: Yes, you're right. I'm reciving an arbitrary string and I will remove everything other than letters and blanks. So everything could be possible :) — user3731513
– user3731513, Commented Jan 30, 2016 at 10:03

Hardik Solanki · Accepted Answer · 2016-01-30 10:12:26Z

1

You need to make one cities array and then check your string against your cities array.

$to_match = array('Bonn','New York','Chicago'); // all cities array

$str = "Bonn New York Chicago"; // Your city string
$new_array = array();

foreach($to_match as $value) {
  if(stristr($str, $value)) {
    $new_array[] = $value;
  }
}

print_r($new_array); // here you can get only match cities against all cities array

This will iterate over each array element, then check if the value of match exists in $str, Then it will add it to $new_array.

answered Jan 30, 2016 at 10:12

Hardik Solanki

3,2151 gold badge21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3731513 Over a year ago

Thanks a lot! It seems, there is no chance to handle it with pure SQL... so, I will take your suggestion. Thanks again for your help!

Collectives™ on Stack Overflow

Mysql WHERE column is in string

cities.name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

cities.name

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related