Iterate over table to find a substring-CodePudding

My table :

TABLE texts: 

(txt TEXT UNIQUE):
    blob
    abcdef
    WhAt7228
    _0jap
...

For txt I want to find substring in a given string (for example "megablob"). if IS_SUBSTR(txt, "megablob"): return true should return true because first element "blob" is a substring of "megablob".

CodePudding user response：

Use EXISTS which returns 1 for true or 0 for false.

For case-sensitive search use INSTR() function:

SELECT EXISTS (SELECT * FROM tablename WHERE INSTR('megablob', txt));

For case-insensitive search use LIKE operator:

SELECT EXISTS (SELECT * FROM tablename WHERE 'megablob' LIKE '%' || txt || '%');