Sphinx search SQl to get exact word

Question

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 ?

Answer 1

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

Answer 2

You could just add comma to charset_table which would mean it indexed as part of words. But would also need to enable infix or prefix matching to be able to match part words, ie dog,dogs,dogs would index as one word.

An alternative might be to turn spaces into say underscores (already in charset_table) and index each tag as a word

dog dogs dogs_and_cats dog_and_cat

Sort of thing. Can just match world tags as words