Glen Swift Glen Swift - 2 years ago 71
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%';
(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

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%';
(1 row)


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

Answer Source

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:

FROM "table"
WHERE '|' || "names" || '|' LIKE '%|bar|%';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download