如何在BigQuery中使用REGEX通过旧列创建新列? [英] How to create a new column through a old one with REGEX in BigQuery?

问题描述

我想通过一个旧的列来创建一个新列.

I would like to create a new column through an old one.

我有此列:name.这是String列.我有这种数据:

I have this column: name. It's a String column. I have this kind of data:

ALUMNNAME_SURNAME_CLASS_UNIVERSITY_YEAR_(16/09 - 22/09)

我想创建每个_分开的新列.

I would like to create new columns split each _.

我知道如何在Google表格中进行操作(INDEX(SPLIT(C2:C;"_");0;1...)，但是如何在BigQuery中进行操作?

In Google Sheets, I know how to do it (INDEX(SPLIT(C2:C;"_");0;1...) but how can I do it in BigQuery?

我了解是这样的:

SELECT
   name,
   REGEXP_EXTRACT(name, regex) AS Name,
   REGEXTRACT(name, regex) AS Surname,
   ...

您能帮我创建RegRx吗?我找不到如何划分每个部分.

Could you help me to create the RegRx? I can't find how to divide each part.

推荐答案

在标准SQL中，我们可以尝试使用SPLIT()函数:

In Standard SQL, we can try using the SPLIT() function:

SELECT
    SPLIT(input, '_')[OFFSET(0)] part1,
    SPLIT(input, '_')[OFFSET(1)] part2,
    SPLIT(input, '_')[OFFSET(2)] part3,
    SPLIT(input, '_')[OFFSET(3)] part4,
    SPLIT(input, '_')[OFFSET(4)] part5
FROM (SELECT "ALUMNNAME_SURNAME_CLASS_UNIVERSITY_YEAR_(16/09 - 22/09)" input)

这篇关于如何在BigQuery中使用REGEX通过旧列创建新列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！