[SOLVED] Making a query which selects the news that contain a specific word

Making a query which selects the news that contain a specific word

       CREATE TABLE IF NOT EXISTS news (
           title TEXT PRIMARY KEY,
           newsletter_description TEXT NOT NULL
       );

I need to write a query which selects all the news that contain the word "apple" or "watermelon"(or both) in their title or in their newsletter_description and I am not very sure about how I can do that. (case insensitive, it can also be "AppLe" or "WaterMelon")

Solution

SELECT * FROM NEWS
WHERE title LIKE "%apple%" OR 
      title LIKE "%watermelon%" OR
      newsletter_description LIKE "%apple%" OR 
      newsletter_description LIKE "%watermelon%

SQlite implemented LIKE operator case insensitive for ASCII characters by default. Unless you use unicode characters in your text you can use above query.

However if you use unicode chars, using lower or upper functions doesn't work either. So there is no point in using lower or upper functions at all.

https://www.sqlite.org/c3ref/strlike.html