Redshift中regexp_instr正则无法匹配换行符的问题排查

需求 #

我需要从Redshift表中筛选出包含特定URL的记录，目标URL对应的字符串示例如下：

• "src=<a href="https://media.hello.com/abc/def.png" rel="nofollow noreferrer">https://media.hello.com/abc/def.png</a>xyz"
• "src=<a href="https://media.hello.com/tys/sdfjkhsdkjf.png" rel="nofollow noreferrer">https://media.hello.com/tys/sdfjkhsdkjf.png</a> xyz"
• "src=https://media.hello.com/tysds/sxsdsa.dfjkhsdkjf.jpeg&xyz"

当前遇到的问题 #

我现在用的查询语句是：

select * from table where regexp_instr(column_name, '(((media.hello.com)|(abcd123.cloudfront.net)).*?)(?=\?(&| |\n))',1,1,0,'ip') > 0

碰到两个问题：

1. 正则里加\n就匹配不到结果，删掉\n能正常返回，但试了[ ]这类写法也没用。
2. 用https?://\S相关的正则也匹配失败，比如下面这两句都不行：

   and column_name ~ 'https?://\S+.(png|jpg)'
   

   and regexp_instr(column_name, 'https?://\S+.(png|jpg)',1,1,0,'ip') > 0
   

解决办法 #

解决换行符匹配问题 #

Redshift用的是POSIX正则引擎，\n写法不生效，且域名里的.没转义会匹配任意字符，得调整。可以用[:space:]字符类匹配所有空白（包括换行、空格），同时给域名中的.转义成\.：

select * from table 
where regexp_instr(column_name, '((media\.hello\.com|abcd123\.cloudfront\.net).*?)(?=\?|&|[:space:])',1,1,0,'ip') > 0

解决https?://\S匹配失败问题 #

POSIX正则不识别\S，得用[^[:space:]]表示非空白字符，另外图片后缀的.也要转义：
用regexp_instr的写法：

select * from table 
where regexp_instr(column_name, 'https?://[^[:space:]]+\.(png|jpe?g)',1,1,0,'ip') > 0

用~操作符的写法：

select * from table 
where column_name ~* 'https?://[^[:space:]]+\.(png|jpe?g)'

~*是不区分大小写匹配，适配性更强。

适配所有示例的综合正则 #

如果要同时匹配指定域名（media.hello.com和abcd123.cloudfront.net）的图片URL，不管后面跟的是&、空格、换行还是其他字符，用这个：

select * from table 
where regexp_instr(column_name, 'https?://(media\.hello\.com|abcd123\.cloudfront\.net)/[^&[:space:]]+\.(png|jpe?g)',1,1,0,'ip') > 0

内容的提问来源于stack exchange，提问作者lakshay