Android Java SQLite searching / matching question

Append and prepend a comma to the string value in the column and then search it for your term which also has comma on either side of it:

   one,two,three,twofold becomes ,one,two,three,twofold,

and you search for

   %,two,%


 where ',' + mycolumn + ',' like '%,two,%'

You don't have to alter ("tamper with") the database record itself.

There is a design problem here. The database structure is not correct for what you're trying to achieve. Storing multiple values in a column and trying to match single values is possible with some other database as MySQL (with the SET type), but it's not on SQLite. So you could try all sort of weird string matching solution, but it won't be clean, and you will not be able to take advantage of SQLite optimizations. It will affect performance.

What you could use instead is a separate words table which contain a unique record for each word and another foobar_words table to establish a many-to-many relation between the words and your main foobar table. Then you could match individual words precisely by using a join. And if there are performance problems, with this schema you should be able to optimize with indexes.

If you don't want to mess with the string, you could modify your sql so that you are looking where mycolumn like 'word,%' or mycolumn like '%,word,%' or mycolumn like '%,word'