Glen Swift Glen Swift - 7 months ago 18
SQL Question

Is there a way to select like with custom separator

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

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|%';
Comments