Find value in comma separated string column through query

Question

I have a field inside a table that contains a string made of the country codes:IT,GB,US,DU,... and I need to check if the customer's country (GB,IT,..) is inside this string.

I know I can use CHARINDEX() in SQL to accomplish this result or retrieve the string, convert it in an array and then check with in_array, but I wonder if there is a way (and if it is better) to do this task during the query.

This is my current code:

Mage::getModel('razorphyn_country/product')->getCollection()
    ->addFieldToFilter('active', 1);
    ->addFieldToFilter('productId', $productId);

And country is the field that contains the string

Answer 1

let's say that the country id you are looking for is $countryId.
And the field name is country_id.
You can add a filter like this:

->addFieldToFilter('country_id', array('finset' => $countryId));

Below the surface this will convert into the use of MySQL string function FIND_IN_SET(str, strlist).