sqlsphinx

Sphinx search SQl to get exact word

My keywords column in table looks like

dog,dogs,dog and cat,dogs and cats

It's a commas separated words

I have try to get exact word now and query like this one is back me result but is not what I need and actually they match all this but I just need to get post ID where is dog, in this example.

SELECT id FROM {$CONF['sphinx_index']} WHERE MATCH('@keywords ",dog" | ",dog," | "dog,"')

In sql this should be easy WHERE (keywords LIKE 'dog,%' OR keywords LIKE '%,dog,%' OR keywords LIKE '%,dog') but for sphinx I can't find solution, anyone have idea how to make query for this ?

Solution 
  • CONCAT('_sep_ ',REPLACE(REPLACE(keywords,' ','_space_'),',',' _sep_ '),' _sep_') AS keywords

    And than search "_sep_ dog _sep_" to match dog or "_sep_ dogs_space_cats _sep_" for dogs and cats