Make Join using a substring

Asked

Viewed 40 times

0

How to Join two tables to check if one string contains another? Ex:

In table A I have the field: AAA;BBB;CCC and in table B I have the field BBB.

How to make a Join like this:

select * from A join B on A.campo contem string B.campo

postgresql

1 answer

0

This form of relationship is not recommended, it would be better with a normalized foreign key, being a unique numerical id. But you could try to put a like

Instead of Join use simple union:

select * from a, b
where a.campo like '%'|| B.campo || '%'

  • ERROR: operator does not exist: record ~~ text LINE 4: Where a."DOC" like '%'|| b.nu_cns || '%' HINT: No operator matches the name and the (s) type of argument(s) reported. You need to add explicit type conversions.

  • fields types are different? tries cast(a. field as varchar) like '%'|| cast(B.field as varchar) || '%'

  • a."DOC" is effectively a table field name a? The error message says no, that a."DOC" is a pseudo type of type record.

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.