SQL Iterate over a table to find a substring

My table in the database looks like this

TABLE texts: 

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

I want to iterate over the txt values and just find out if at least one of them is a substring in a given string(for example megablob).

Something like if IS_SUBSTR(txt, "megablob"): return true , should return true because the 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 || '%');

Leave a Reply