14 Oct 18 · Bozhack-miller ·       Add to Favorites   Report

How can I split a string using regular expression in BigQuery

I have many values in a column separated by hyphens and i also need to split based on commas into a separate row, Consider Sample data in my column:

"idx1-cnt1-name1,idx2-cnt2-name2... same pattern"

I want to convert to output like below

Row idx cnt name
1 idx1 cnt1 name1
2 idx2 cnt2 name2    
#legacySQL
SELECT
REGEXP_EXTRACT(split_col, r'^(.*?)-.*?-.*?$') AS idx,
REGEXP_EXTRACT(split_col, r'^.*?-(.*?)-.*?$') AS cnt,
REGEXP_EXTRACT(split_col, r'^.*?-.*?-(.*?$)') AS name
FROM (
SELECT SPLIT(source_field, ',') split_col
FROM (SELECT "idx1-cnt1-name1,idx2-cnt2-name2" source_field)
)

Result:

Row idx cnt name
1 idx1 cnt1 name1
2 idx2 cnt2 name2    

Bozhack-miller

posted on 14 Oct 18

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied