1

I've found this query : 

CREATE TABLE tableA (string_a text);
INSERT INTO tableA(string_a) VALUES 
('the manual is great'), ('Chicken chicken chicken'), ('bork');

CREATE TABLE tableB(candidate_str text);
INSERT INTO tableB(candidate_str) VALUES
('man'),('great'),('chicken');

SELECT string_a 
FROM tableA 
WHERE string_a LIKE ANY (SELECT '%'||candidate_str||'%' FROM tableB);

Result :

the manual is great
chicken chicken chicken

Question : How to do to have this new result ? 

the manuel is great      | great
chicken chicken chicken  | chicken
Community
  • 1
  • 1
Leasye
  • 261
  • 1
  • 8
  • 19

1 Answers1

1

Use a JOIN:

SELECT a.string_a, b.candidate_str
FROM tableA a
  JOIN tableB b ON a.string_a LIKE '%'||b.candidate_str||'%';

Note that this will show the row with the manual is great twice, because the candidate_str matches the man in manual and the word great. This could be changed by e.g. using distinct on to only return rows from tableA once.

  • Do note that you are using a front *and* back wildcard with LIKE. This will destroy any index usage, which is a bad idea. Why not use PostgreSQLs full text search capabilities for this instead? – Timusan Aug 18 '14 at 07:47
  • @Timusan: that is a valid point but you should have addressed that to leasye Btw: there *is* an index type in Postgres that can be used with such a LIKE condition: http://www.depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike/ –  Aug 18 '14 at 07:49
  • @a_horse_with_no_name: Ah, sorry about that, must still be sleeping, did not see the wildcards in the original query. Hmm, that index type is indeed news to me (thanks for the heads-up), sounds tempting, but still prefer FTS in Leasye's case. – Timusan Aug 18 '14 at 07:57