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

I don't like this behaviour of accepting an answer and then un-accept but it's fine and you have all rights to choose the best one. — Sujitmohanty30, Sep 29 '20 at 04:31

Sujitmohanty30 · Answer 1 · 2020-09-28T18:10:53.133

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%';

score 0 · Answer 2 · answered Sep 28 '20 at 18:07

0

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

answered Sep 28 '20 at 18:07

pixel

1
4

And how can I use it in order to find all of the words within the query, in other words, how the full query command would look like? – Esmer Omer Sep 28 '20 at 18:09
Select * from news where lower(title) like '%apple%' or lower(title) like '%watermelon%'. If you use a other where clause like a date or something else, put the request in parentheses like it : select * from ... where (lower(title) ... or lower(title)...) and date = '2020101' – pixel Sep 28 '20 at 18:11

ozanmut · Accepted Answer · 2020-09-28T18:37:00.283

0

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

edited Sep 28 '20 at 18:37

answered Sep 28 '20 at 18:23

ozanmut

2,898
26
22

your answer is quite detailed with respect to SQlite but instead of focusing on other answer whether it makes point or or not try telling the positive side of it and I am sure there is apart from `no point` part of not using `lower` – Sujitmohanty30 Sep 29 '20 at 04:33
What i wanted to emphasize was case insensitivity of Sqlite. I saw people mention lower/upper function in many places in this thread. So, to prevent people having wrong impression/knowledge remain in their mind, I felt to mention it specifically. I don't have any other intention otherwise. Thanks for your comment. – ozanmut Sep 29 '20 at 12:58

forpas · Answer 4 · 2020-09-28T19:07:24.900

0

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

edited Sep 28 '20 at 19:07

answered Sep 28 '20 at 18:31

forpas

160,666
10
38
76

score 0 · Answer 5 · answered Sep 28 '20 at 19:50

0

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%’;

answered Sep 28 '20 at 19:50

swang41

1

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

5 Answers5