Hi,

I have a table which stores keywords as a comma separated list. The records are like

1) Ad Name | car rentals, car advertising
2) Ad Name | car, bmw, bike


I would want to search for 'car' and would like to have only the second record listed and not the first record. But our usual query where keyword like '%car%' wouldn't work here. Is it possible to split these keywords in the query itself and then make it work?

I hope I was clear in explaining my need.

you should not have a table which stores keywords in a comma-separated list, because this violates first normal form

you will find that the queries will be much easier to construct if you have a table which stores one row per keyword

No, that was a question asked to me by a fellow friend and his job was to import data from a existing db to a new db. ( couldn't argue with them )

Anyway, I came up with a query.. Not sure whether it would work always..

select * from ads where (ad_words like '^car,' or ad_words regexp ',car,' or ad_words regexp ',car$' or ad_word regexp 'car')

seemed to work with the present db.

if it works, i'll take your word for it, but it will never scale, because it is forced to do a table scan

the more rows your table has, the slower the query gets

good luck