Filtering selecting column with like clause from unrelated another table - Postgresql?

Question

I have two unrelated tables. They both have varchar columns. If all rows in the text column of table B are in the text column of table A, I want to filter it like "Full Text Search".

For example:

A column of rows:

1- ABCD

2- DBCA

3- ACBD

B column of rows:

1- BC

Expected output at the end of the query:

3- ACBD

It's nonsensical but just for explanation:

select text from table1 where text ilike concat('%', select text from table2, '%')

How do you think I can do this query in the most efficient way?

Answer 1

You can do this without like '%BC%' , using just plain string matching. (don't expect it to be fast; you'll need trigrams for performance)

---

CREATE TABLE aaa
        ( ii integer not null primary key
        , vv varchar
        );
INSERT INTO aaa ( ii , vv ) VALUES ( 1, 'ABCD' ) , ( 2, 'DBCA' ) , ( 3, 'ACBD' );

CREATE TABLE bbb
        ( ii integer not null primary key
        , vv varchar
        );
INSERT INTO bbb ( ii , vv ) VALUES ( 1, 'BC' ) ;

SELECT * FROM aaa a
WHERE EXISTS (
        SELECT * FROM bbb b
        -- WHERE POSITION (b.vv IN a.vv) > 0 
        WHERE NOT POSITION (b.vv IN a.vv) > 0
        );

---

Results:

---

CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 1
 ii |  vv  
----+------
  3 | ACBD
(1 row)