How to determine if lists have at least one same element

Question

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

[Dont' use char](https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_char.28n.29). For this specific situation, an array (`text[]`) or `jsonb` column would be better rather than the dreaded delimited strings — , Apr 28 '22 at 12:07

score 1 · Accepted Answer · answered Apr 28 '22 at 11:53

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

How to determine if lists have at least one same element

1 Answers1