如何在Redshift中使用正则表达式从URL提取top_ids对应的数字ID

核心实现代码 #

直接对存储URL的字段使用如下regexp_substr写法即可提取目标数字ID：

regexp_substr(你的URL字段名, 'top_ids%3D(\\d+)', 1, 1, 'e') as top_id

逻辑说明 #

• 正则规则top_ids%3D(\\d+)：用固定锚点top_ids%3D精准定位参数位置，括号内的\\d+匹配连续数字，就是你需要的ID值
• 函数参数说明：
  • 第1个参数：存储URL的字段名
  • 第2个参数：匹配用的正则表达式
  • 第3个参数1：从URL第1位开始匹配
  • 第4个参数1：取第一个匹配到的结果
  • 第5个参数'e'：开启提取捕获组内容的模式，直接返回括号内匹配的数字，不需要额外做截取

测试示例 #

针对你给出的示例URL，测试语句如下：

select 
  regexp_substr('/checkout?feature=ADVANCED_SEARCH&upgradeRedirect=%2Fmentions%3Ftop_ids%3D1222874068&btv=feature_ADVANCED_SEARCH', 'top_ids%3D(\\d+)', 1, 1, 'e') as top_id1,
  regexp_substr('/checkout?feature=ADVANCED_SEARCH&trigger=mentioning-author-rw&upgradeRedirect=%2Fmentions%3Ftop_ids%3D160447990', 'top_ids%3D(\\d+)', 1, 1, 'e') as top_id2

运行结果：

异常处理 #

如果存在URL中没有top_ids参数、参数后无有效数字的异常场景，函数会返回NULL，你可以根据需求用coalesce函数设置默认值即可。

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