Is there a way to select like with custom separator

Question

I have postgresql table that looks like this:

+----+---------------------+
| id |        names        |
+----+---------------------+
|  1 | foo|bar and biz|pop |
+----+---------------------+

I want to select row containing given name. Something like

SELECT "id" FROM "table" WHERE "names" LIKE '%foo%';
 id
-----
1
(1 row)

I want the query to return this row as well if I ask for bar and biz but return nothing if I ask from bar.

For now I'm adding pipe symbols to the beginning and the end of the line and ask LIKE '%|bar and biz|%'. Anyway, I wonder is there a way to find that row without additional pipes.

Is there a way to do such query in postgresql?

UPD: It seems like I explain my problem bad. Well, I want following:

SELECT "id" FROM "table" WHERE "names" LIKE '%bar and biz%';
 id
-----
1
(1 row)

and

SELECT "id" FROM "table" WHERE "names" LIKE '%bar%';
 id
-----
(0 rows)

Answer 1

First, storing multiple values in a single column is a bad idea:

• SQL is not very good at string operations.
• Such operations cannot make use of indexes.
• You cannot use foreign key relationships to validate values.

Instead, you should be using a junction table. Postgres also has other solutions for storing lists, such as arrays and JSON.

Sometimes, we are stuck with other people's bad design decisions. One method using like is:

SELECT "id"
FROM "table"
WHERE '|' || "names" || '|' LIKE '%|bar|%';