如何对包含用逗号分隔值的字符串的列值执行搜索查询？

问题描述：

我有一个类似下面的表格

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.

例如，我有一个字符串列表 [ down， 110.22.100.3] 。现在，如果我查看包含列表中字符串的表，我应该得到最后两行，它们的名称分别为 ZONE2，ZONE1 。

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.

现在我知道在$code>运算符中有一个叫做的东西，但是我不太确定如何在这里使用它。

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

我尝试了以下操作

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

但是我收到语法错误。该怎么办？

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

答

您可以执行以下操作。

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

1）删除现有字符串中的空格以进行适当的string_to_array分隔，而在前面没有任何空格，请使用replace

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 将您的字符串转换为以逗号分隔的数组。

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

3） @> 是包含运算符

（OR）

如果要整体匹配

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

关于位置的更多信息此处

如何对包含用逗号分隔值的字符串的列值执行搜索查询？

相关推荐