时间戳

首页 > 解决方案 > 创建新列，所有内容都在另一列的第三和第四之间

sql - 创建新列，所有内容都在另一列的第三和第四之间

问题描述

我需要根据该列创建一个新列col，但提取第三-和第四之间的所有内容-。例子：
ABC-123-aaa-INEEDTHIS-2000-BBB-123-CCC
111-AAAAA-bb-INEEDTHIS-BB-435-A

如何使用 Big Query 做到这一点？
我正在尝试这样的事情：

SELECT REGEXP_EXTRACT(col, r'\w\w[^\d]\d\d') as newcol from mytable

如果可能的话，我也想了解regex这个解决方案的背后。

标签： sqlregexgoogle-bigquery

解决方案

以下是 BigQuery 标准 SQL

最简单的方法是使用 SPLIT 函数，如下例所示

SELECT SPLIT(col, '-')[SAFE_OFFSET(3)] AS newcol

如果由于某种原因您想使用正则表达式 - 您可以使用 REGEXP_EXTRACT ，如下例所示

REGEXP_EXTRACT(col, r'(?:[^-]*-){3}([^-]+)') AS newcol

您可以使用您问题中的示例数据来测试和使用上述两种方法，如下例所示

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'ABC-123-aaa-INEEDTHIS-2000-BBB-123-CCC' col UNION ALL
  SELECT '111-AAAAA-bb-INEEDTHIS-BB-435-A'
)
SELECT 
  col, 
  SPLIT(col, '-')[SAFE_OFFSET(3)] AS newcol_with_split,
  REGEXP_EXTRACT(col, r'(?:[^-]*-){3}([^-]+)') AS newcol_with_regexp
FROM `project.dataset.table`

带输出

Row col                                     newcol_with_split   newcol_with_regexp   
1   ABC-123-aaa-INEEDTHIS-2000-BBB-123-CCC  INEEDTHIS           INEEDTHIS    
2   111-AAAAA-bb-INEEDTHIS-BB-435-A         INEEDTHIS           INEEDTHIS

正则表达式的简要说明

  1. [^-]*-捕获诸如ABC-, 123-, aaa- 等条目。
  2. (?:[^-]*-)确保不会提取这些条目
  3. (?:[^-]*-){3}确保将跳过前三个此类条目
  4. 最后，([^-]+)提取目标片段直到下一个-

推荐阅读