Return rows where the full string in columnA exists as substring in ANY row of columnB

Question

INSERT INTO names
    (`id`,`columnA`,`columnB`)
VALUES
    (1,'john','dog'),
    (2,'orange','john smith'),
    (3,'alex','alex'),
    (4,'match','man'),
    (5,'pony','orange')

For the dataset above, I'm trying to write a SQL query that returns rows id's 1,2, and 3. These three id's have values in columnA that exist as a substring in ANY row of columnB.

• john in row 1 (columnA) exists as a substring in john smith row 2 (columnB)
• orange in row 2 (columnA) exists as a substring in orange row 5 (columnB)
• alex in row 3 (columnA) exists as a substring in alex row 3 (columnB)

Answer 1

You can concatenate a wildcard on a column name so that SQL searches for the string within a larger string.

SELECT * FROM names WHERE '%'+ columnA + '%' in (select '%' + columnB + '%' from cte)

Answer 2

Try below few options

select any_value(a).*
from your_table a, your_table b
group by to_json_string(a)
having logical_or(b.columnB like '%' || a.columnA || '%')

or/and

select * 
from your_table
where true
qualify string_agg(columnB, '|||') over() like '%' || columnA || '%'       

If applied to sample data in your question - output (for both above options) is