1

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)
Glen Swift
  • 11,879
  • 14
  • 45
  • 77

5 Answers5

2

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|%';
Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709
  • To be honest this solution is mine and I've created this column just a moment ago. I tried to created something as simple as I can to keep aliases in table but you're right, the discussion here and your sql snippet point me that my idea was completely wrong. I better rewrite this to be array or something. Thank you for knowledge sharing. – Glen Swift Apr 17 '16 at 12:28
2

While stuck with your unfortunate design, convert to an array and use the ANY construct:

SELECT id
FROM   table
WHERE  'bar' = ANY (string_to_array(names, '|'));

About ANY, @>, arrays and indexes:

Community
  • 1
  • 1
Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
1

Did you try the following?

where "names" = 'foo' or
      "names" LIKE 'foo|%' or 
      "names" LIKE '%|foo' or
      "names" LIKE '%|foo|%'
Giorgi Nakeuri
  • 34,370
  • 8
  • 37
  • 69
1

Since your column is already separated with '|' you could cast it to an array an use the contains to get your desired result

SELECT "id"
FROM "table"
WHERE string_to_array(names, '|')::varchar[] @> '{bar}'::varchar[];
Alex
  • 805
  • 13
  • 23
0

I just checked in my system and it is working fine for me. First of all i don't understand why you have double quotes in column name. It's not recommended.

My table schema

CREATE TABLE table (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  names varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And to fetch data, i used this

SELECT id FROM table WHERE names LIKE '%foo%' or names LIKE '%bar and biz%';

Update:-

SELECT * FROM A WHERE names LIKE '%bar%';

enter image description here

Remove double quotes from coulmn names and table names.

Naruto
  • 4,111
  • 1
  • 20
  • 31
  • Seems like I explain my problem bad. Pls, check out update in the question. Btw I use double quotes because I have camel-case named columns and postresql automatically converts everything to lowercase in case there are no double quotes around it. – Glen Swift Apr 17 '16 at 12:13