Searching substring in PostgreSQL (part II)

Question

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

Please define which row to pick from tableB in case of multiple matches. Also, do you expect few or many matches per row? — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 17, 2014 at 21:40

user330315 · Accepted Answer · 2014-08-17 18:51:17Z

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.

answered Aug 17, 2014 at 18:51

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Timusan Over a year ago

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?

user330315 Over a year ago

@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: depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike

Timusan Over a year ago

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

Collectives™ on Stack Overflow

Searching substring in PostgreSQL (part II)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related