CODEWITHSUNDEEP
sqlsqlitesubstringsql-likeexists
baronsec
baronsec

Reputation: 224

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

Upvotes: 1

Views: 113

Answers (1)

forpas
forpas

Reputation: 164089

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

Upvotes: 3

Related Questions