CODEWITHSUNDEEP
postgresql
StellaMaris
StellaMaris

Reputation: 867

Join tables on string value

I have two tables A and B. Each of them has a column "name" of type string. I want to get all records from joining operation where A.name is a sub string of B.name.

select * from A inner join B on A.name like concat('%',B.name,'%');

The result is a empty table. Something goes wrong with that query, because I got results if I test it via

select * from A cross join B where A.name like 'test' and B.name like '%test%';

There for I got some records for the static value "test".

Upvotes: 2

Views: 9268

Answers (2)

Fabricator
Fabricator

Reputation: 12782

The first query is matching B as substring of A. You need:

select * from A inner join B on B.name like concat('%',A.name,'%');

or

select * from A inner join B on position(A.name in B.name) > 0;

Upvotes: 7

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

If you want to match A.name being a substring of B.name, then then expression is B.name LIKE '%A.name%'. Try this:

select *
from A inner join B
on B.name like '%A.name%'

Upvotes: 0

Related Questions