CODEWITHSUNDEEP
postgresqlselectsearch
Souvik Ray
Souvik Ray

Reputation: 3018

How to perform a search query on a column value containing a string with comma separated values?

I have a table which looks like below

    date    |                            tags   |   name               
------------+-----------------------------------+--------         
 2018-10-08 | 100.21.100.1, cpu, del               ZONE1
 2018-10-08 | 100.21.100.1, mem, blr              ZONE2
 2018-10-08 | 110.22.100.3, cpu, blr               ZONE3
 2018-10-09 | 110.22.100.3, down, hyd              ZONE2
 2018-10-09 | 110.22.100.3, down, del              ZONE1

I want to select the name for those rows which have certain strings in the tags column

Here column tags has values which are strings containing comma separated values.

For example I have a list of strings ["down", "110.22.100.3"]. Now if I do a look up into the table which contains the strings in the list, I should get the last two rows which have the names ZONE2, ZONE1 respectively.

Now I know there is something called in operator but I am not quite sure how to use it here.

I tried something like below

select name from zone_table where 'down, 110.22.100.3' in tags;

But I get syntax error.How do I do it?

Upvotes: 1

Views: 1907

Answers (3)

S-Man
S-Man

Reputation: 23676

demo: db<>fiddle

I would do a check with array overlapping (&& operator):

SELECT name 
FROM zone_table 
WHERE string_to_array('down, 110.22.100.3', ',') && string_to_array(tags,',')
  1. Split your string lists (the column values and the compare text 'down, 110.22.100.3') into arrays with string_to_array() (of course if your compare text is an array already you don't have to split it)
  2. Now the && operator checks if both arrays overlap: It checks if one array element is part of both arrays (documentation).

Notice:

  1. "date" is a reserved word in Postgres. I recommend to rename this column.
  2. In your examples the delimiter of your string lists is ", " and not ",". You should take care of the whitespace. Either your string split delimiter is ", " too or you should concatenate the strings with a simple "," which makes some things easier (aside the fully agreed thoughts about storing the values as string lists made by @TimBiegeleisen)

Upvotes: 1

Vishal Raghavan
Vishal Raghavan

Reputation: 483

You can do something like this.

select name from zone_table where 
string_to_array(replace(tags,' ',''),',')@>
string_to_array(replace('down, 110.22.100.3',' ',''),',');

1) delete spaces in the existing string for proper string_to_array separation without any spaces in the front using replace

2)string_to_array converts your string to array separated by comma.

3) @> is the contains operator

(OR)

If you want to match as a whole

select name from zone_table where POSITION('down, 110.22.100.3' in tags)!=0

For separate matches you can do

select name from zone_table where POSITION('down' in tags)!=0 and 
POSITION('110.22.100.3' in tags)!=0

More about position here

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521269

We can try using the LIKE operator here, and check for the presence of each tag in the CSV tag list:

SELECT name
FROM zone_table
WHERE ', ' || tags LIKE '%, down,%' AND ', ' || tags LIKE '%, 110.22.100.3,%';

enter image description here

Demo

Important Note: It is generally bad practice to store CSV data in your SQL tables, for the very reason that it is unnormalized and makes it hard to work with. It would be much better design to have each individual tag persisted in its own record.

Upvotes: 1

Related Questions