Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE classi (classification text);
INSERT INTO classi VALUES ('Residential'), ('Apartment'), ('Commercial');

CREATE TABLE test AS
SELECT 'Apartment,Residential'::text AS b;
3 rows affected

1 rows affected

SELECT c.*
FROM   classi c
JOIN   test t ON c.classification = ANY (string_to_array(t.b, ','));

classification

Residential

Apartment

SELECT c.*
FROM   classi c
WHERE  EXISTS (
   SELECT FROM test t
   WHERE  c.classification = ANY (string_to_array(t.b, ','))
   );

classification

Residential

Apartment

-- add dupes
INSERT INTO test VALUES ('Residential,Apartment'), ('Apartment,Apartment,Residential,Apartment');
2 rows affected

-- returns dupes
SELECT c.*
FROM   classi c
JOIN   test t ON c.classification = ANY (string_to_array(t.b, ','));

classification

Residential

Residential

Residential

Apartment

Apartment

Apartment

-- prevent duplication
SELECT c.*
FROM   classi c
WHERE  EXISTS (
   SELECT FROM test t
   WHERE  c.classification = ANY (string_to_array(t.b, ','))
   );

classification

Residential

Apartment