类Hive匹配词边界

Question

我是Hive regex匹配的新手，正在努力寻找匹配单词边界的正确模式:

haystack RLIKE concat('(?i)b', 'needle', 'b')

不返回任何值

样本值，我有在DB:

haystack
---------
needless to say
this is a needle
so many (needle)
these are needles

当我使用haystack RLIKE concat('(?i)', 'needle')时，它返回给我所有的行，但我实际上是在寻找this is a needle。

Answer 1

在Hive中使用两个反斜杠:\b

演示:

with mytable as (
select stack(4,
'needless to say',
'this is a needle',
'so many (needle)',
'these are needles'
) as haystack
)
select haystack, haystack rlike concat('(?i)\b', 'needle', '\b') from mytable;

结果:

haystack             _c1
needless to say      false
this is a needle     true
so many (needle)     true
these are needles    false

注意so many (needle)也匹配，因为(和)不是单词字符。