How to Find Exact String in a Sentence – SQLite Query Performance

performancequeryquery-performancesqlitestring-searching

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

enter image description here

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?

Best Answer

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:

enter image description here

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

Related Question