Android Java SQLite searching / matching question

So I'm trying to search through a column where the string is a comma separated list of words with no spaces such as one,two,three,four and so on. Prob开发者_开发技巧lem I'm facing is that I'm trying to match an exact word in the string. As of now I'm simply using %word% to find a match which of course works but the problem is it isn't exact in that if I searched for 'word' it would also match 'words', 'worded' and so on. So question is how can I search and get the exact word?

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,%'

edit: in SQLite replace '+' with '||' (pipe pipe): where ',' || mycolumn || ',' like  '%,two,%'

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

P.S. Wildcard on both sides of search term eliminates possibility of using an index to speed the search, so this approach is not recommended on large datasets. But then again, neither is a comma-delimited list :-)

P.P.S. I don't know what device you're using, of course, or the size of your table, but on a PC several years old it takes SQlite 8ms to search through 3000 records using this comma-concatenation approach.

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'