sqlpostgresqlselectintersectionset-intersection

How to determine if lists have at least one same element

Product table contains tags columns:

create table product(
  product char(20) primary key,
  tag1 char(100),
  tag2 char(100) )

tag columns contain tags separated by semicolon like

AB;AC;AD
RXX;AC;XAD
RP12;X455;R444;AXD

How to find all rows where tag1 and tag2 columns contain at least one same tag ?

For example row

tag1        tag2
AB;AC;AD    RXX;ZAC;XAD;AC

should be in result since both columns contain AC

Row

tag1        tag2
AB;XAC;AD    RXX;ZAC;XAD;AC

Should not be in result since all tags are different.

Using PostgreSQL 13.2

Solution

  • You can use the && operator to check if there are common elements in the columns. To do this, you must first convert the string to an array using the string_to_array

    SELECT * FROM my_table WHERE  (string_to_array(tag1,';')::text[]) && (string_to_array(tag2,';')::text[])

    Demo in DBfiddle