Impala查询:在管道分隔列表中查找值 [英] Impala Query: Find value in pipe-separated list

问题描述

我有一列，其中包含以行分隔的STRING值的行:

I have a column containing rows of pipe separated STRING values:

|   colA    |
 ___________
| 5|4|2|255 |
| 5|4|4|0   |
| 5|4|4|3   |
| 5|4|4|4   |

我需要创建一个查询，该查询将选择包含4或5但从不包含2或3的所有行.

I need to create a query that will select all rows that contain 4 or 5, but never 2 or 3. Something along the lines of:

SELECT t.colA
FROM my_table t
WHERE (t IN ("4", "5") AND t NOT IN ("2","3")

结果:

|   colA    |
 ___________
| 5|4|4|0   |
| 5|4|4|4   |

我最终使用了以下两个答案的组合，因为单独使用这两种方法仍然使我仅包含"255"的行.这是最终的查询:

I ended up using a combination of the two answers below, as using either method alone still left me with rows containing only "255". Here's the final query:

SELECT t.colA
FROM my_table t
WHERE (t.colA IN ('4', '5') OR t.colA LIKE "%|5|%" 
       OR t.colA LIKE "%|5" OR t.colA LIKE "5|%")
AND t.colA NOT LIKE "%3%"    
AND t.colA NOT LIKE "%|2|%" 
AND t.colA NOT REGEXP "^2|%" 
AND t.colA NOT REGEXP "%|2$"

也许有一种更优雅的方法可以做到这一点，但这确实可以解决问题.

There is probably a more elegant way to do this, but that does the trick.

推荐答案

使用那应该做的.

这篇关于Impala查询:在管道分隔列表中查找值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！