CODEWITHSUNDEEP
sqlpostgresqlsubstring
Leasye
Leasye

Reputation: 279

Searching substring in PostgreSQL (part II)

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

Upvotes: 1

Views: 62

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 1

Related Questions