CODEWITHSUNDEEP
regexgoogle-bigquery
nshabi
nshabi

Reputation: 63

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.

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 _.

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

I understand it's something like this:

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

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

Upvotes: 0

Views: 333

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

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)

Upvotes: 1

Related Questions