Search code examples
sqlsqlitesubstringsql-likeexists

Iterate over table to find a substring

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

Solution

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