BigQuery: SPLIT() returns only one value

google-bigquery

Solution

2018 standardSQL update:

#standardSQL
SELECT SPLIT(path, '/')[OFFSET(0)] part1,
       SPLIT(path, '/')[OFFSET(1)] part2,
       SPLIT(path, '/')[OFFSET(2)] part3
FROM (SELECT "/a/b/aaaa?c" path)

Now I understand you want them in different columns.

An alternative to the query you provided:

SELECT FIRST(SPLIT(path, '/')) part1,
       NTH(2, SPLIT(path, '/')) part2,
       NTH(3, SPLIT(path, '/')) part3
FROM (SELECT "/a/b/aaaa?c" path)

`NTH(X, SPLIT(s))` will provide the Xth value from the SPLIT. `FIRST(s)` is the same as `NTH(1, s)`

Problem

I have a page URL column components of which are delimited by `/`. I tried to run the `SPLIT()` function in BigQuery but it only gives the first value. I want all values in specific columns. I don't understand how to use the `Regexp_extract()` example mentioned in Split string into multiple columns with bigquery. I need something similar to `REGEX_SPLIT_TO_TABLE(<String>, <DELIMITER>)` which converts a single string into multiple columns. Query: ``` SELECT PK, DATE(TIMESTAMP(CONCAT(SUBSTR(date,1,4),'-',SUBSTR(date,5,2),'-',SUBSTR(date,7,2),' 00:00:00'))) as visit_date, hits_page_pagePath, split(hits_page_pagePath,'/') FROM [Intent.All2mon] limit 100 ```

Original source

Related problems