BigQuery: SPLIT () only returns one value
I have a page url column that are limited /
. I tried to run a function SPLIT()
in BigQuery, but it only gives the first value. I want all values to be in specific columns.
I don't understand how to use the example Regexp_extract()
mentioned in Split a row into multiple columns using bigquery .
I need something similar to REGEX_SPLIT_TO_TABLE(<String>, <DELIMITER>)
that converts one row to 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
source to share
Now I understand that you want them in different columns.
Alternative to the provided request:
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 X value from the SPLIT. FIRST(s)
coincides withNTH(1, s)
source to share
Solved it somehow.
SELECT
date,
hits_time,
fullVisitorId,
visitNumber,
hits_hitNumber,
X.page_path,
REGEXP_EXTRACT(X.page_path,r'/(\w*)\/') as one,
REGEXP_EXTRACT(X.page_path,r'/\w*\/(\w*)') as two,
REGEXP_EXTRACT(X.page_path,r'/\w*\/\w*\/(\w*)') as three,
REGEXP_EXTRACT(X.page_path,r'/\w*/\w*/\w*\/(\w*)\/.*') as four
from
(
select
date, hits_time, fullVisitorId, visitNumber, hits_hitNumber,
REGEXP_REPLACE (hits_page_pagePath, '-', '') as page_path
from
[Intent.All2mon]
) X
limit 1000
source to share
in standard sql, you can use the following functions:
array[OFFSET(zero_based_offset)]
array[ORDINAL(one_based_ordinal)]
So
SELECT SPLIT(path, '/')[OFFSET(1)] part2,
SPLIT(path, '/')[ORDINAL(2)] part2_again,
SPLIT(path, '/')[ORDINAL(3)] part3
FROM (SELECT "/a/b/aaaa?c" path)
part2 part2_again part3
a a b
part1
in this case is the empty string (before the first forward slash)
source to share