How to find exact string from a sentence I'm using SQLITE and I'm trying to find the exact word on a sentence, but I'm not getting just the word I'm getting for instance if I'm looking for Water I'm getting also aWater, wateri24 and that's not what I'm looking for.

I've created an example here on sqliteonline to show you how do I store the sentences, there are in table b.

This is an example of what is happening



NOTE: I'm looking for an answer that uses ignore cases, for instance if I want to find water I want to get wAtER and WatER, and so...

What I'm missing?
You could do something like this:

When considering that a word like water can only be surrounded by spaces, comma's and square brackets.

SELECT * FROM b WHERE list LIKE '%[water,%'
OR list LIKE '%, water,%'
OR list LIKE '%, water]%';


And you could technically remove some of the wildcards:

SELECT * FROM b WHERE list LIKE '[water,%'
OR list LIKE '%, water,%'
OR list LIKE '%, water]';


SQLliteOnline example #1

SQLliteOnline example #2

Both returning:




  NOTE: I'm looking for an answer that uses ignore cases, for instance
  if I want to find water I want to get wAtER and WatER, and so...


Like operator should be case sensitive.


  Note that SQLite LIKE operator is case-insensitive. It means "A" LIKE
  "a" is true. However, for Unicode characters that are not in the ASCII
  range, SQLite LIKE operator is case sensitive e.g., "Ä" LIKE "ä" is
  false.


Source

If it wasn't, COLLATE NOCASE could be used
More on that here