2

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?

  • No, it doesn't work. – Skizo-ozᴉʞS Jul 11 '19 at 15:30
  • Have you tried the solution with GLOB. I have provided a demo link in my answer? – Milan Jul 11 '19 at 16:58
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:

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

  • 1
    Thanks Randi exactly what I was looking for! – Skizo-ozᴉʞS Jul 9 '19 at 21:23
  • 1
    @Skizo-ozᴉʞS Great! Glad it helped :) – Randi Vertongen Jul 9 '19 at 21:24
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.