Search for zero or one occurrence of word in SQL row

Question

I want to search for the occurrence of words in a database row. I use SQLite as a database. The search needs to be case-insensitive.

Let's say row x is Hello. Today is Monday and I want to search for Hello Monday Bye and still return the row x because the Hello and Monday exist in the row.

I used Like operator %Hello%Tuesday%Bye% but this defiantly doesn't work and I can't use Glob because it's not case-insensitive. Any suggestion, how can I do the search?

Also, the order shouldn't matter and Monday Hello should return a row as well

What do you mean by *zero* occurrence? If none of the words exist in the column what do you want in the results? — forpas, Nov 16 '20 at 16:37
if non of them exists I want to return an empty result forpas — taraf, Nov 16 '20 at 16:44

score 1 · Answer 1 · answered Nov 16 '20 at 16:59

1

You can extend SQLite with regular expressions and then use:

where col regexp replace($searchfor, ' ', '|')

This will return any time there is a match with any of the values.

answered Nov 16 '20 at 16:59

Gordon Linoff

1,242,037
58
646
786

forpas · Answer 2 · 2020-11-16T18:03:06.047

First create a recursive CTE to split the string 'Hello Monday Bye' which contains the words you search for.
Then join it to the table:

with cte as (
    select null word, 'Hello Monday Bye' || ' ' rest
    union all
    select substr(rest, 1, instr(rest, ' ') - 1),
           substr(rest, instr(rest, ' ') + 1)
    from cte
    where length(rest) > 0
  )
select distinct t.* 
from tablename t inner join cte c
on t.col like '%' || c.word || '%'

See the demo.

Search for zero or one occurrence of word in SQL row

2 Answers2