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

Question

       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")

Answer 1

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

Answer 2

You can use like operator and to have case insensitive search you can either use lower or upper on the actual column and also have to convert the input to lower/upper before passing to the query accordingly,

select * 
  from news 
 where lower(newsletter_description) like '%watermelon%' 
    or lower(newsletter_description) like '%apple%'
    or lower(title) like '%watermelon%' 
    or lower(title) like '%apple%';
    

Answer 3

You can use « lower(title) like '%apple%' » In fact the lower put all the field in minuscule, that help you to find the word needed without knowing how he is written

Answer 4

Naive way will be select * from new where lower(title) like ‘%apple%’ or lower(title) like ‘%watermelon%’ or lower(newsletter_description) like ‘%apple%’ or lower(newsletter_description) like ‘%watermelon%’;

Answer 5

Use a CTE that returns all the words that you search for and join it to the table:

with cte(word) as (values ('apple'), ('watermelon'))
select n.*
from news n inner join (select '%' || word || '%' word from cte) c
on n.title like c.word or n.newsletter_description like c.word