How to find exact string from a sentence

Question

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?

Answer 1

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

Answer 2

Solution to find rows containing 'the exact word' in a sentence:

SELECT * FROM b
WHERE ' '||LOWER(list)||' '  
    GLOB '*[^a-zA-Z]water[^a-zA-Z]*';

Here we have used a negative match of a set of characters, before and after the desired word.
This and wrapping the column in spaces ensures that the word is found even if it is at the begining or the end of the sentence.
Note that I have had to use LOWER(), because of GLOB, which is case sensitive.

I have uploaded a sqlite demo for you to try.

This other script is for any Database with available REGEXP in LIKE operator:

SELECT * FROM table  
WHERE ' ' + column + ' '  
    LIKE '%[^A-Za-z]word[^A-Za-z]%';  
-- you should replace 'table', 'column', 'word' to your liking  

Note: REGEXP solution taken from article "wildcard searches" on SQL Server Central, posted by Luis Cazares.