CODEWITHSUNDEEP
google-bigquery
bcascone
bcascone

Reputation: 139

How to Parse simple data in BigQuery

I need to do a simple parse of some data coming from a field. Example:

1/2
1/3
10/20
12/31

I simply need to Split or Parse this on "/". Is there a simple function that will allow me to do this?

Upvotes: 0

Views: 2095

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Below example is for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, '1/2' list UNION ALL
  SELECT 2, '1/3' UNION ALL
  SELECT 3, '10/20' UNION ALL
  SELECT 4, '15/' UNION ALL
  SELECT 5, '12/31' 
)
SELECT id, 
  SPLIT(list, '/')[SAFE_OFFSET(0)] AS first_element,
  SPLIT(list, '/')[SAFE_OFFSET(1)] AS second_element
FROM `project.dataset.table`
-- ORDER BY id

with result as below 

Row id  first_element   second_element   
1   1   1               2    
2   2   1               3    
3   3   10              20   
4   4   15       
5   5   12              31

Upvotes: 2

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14014

Check the following SQL functions:

For example with SPLIT you can do

SELECT parts[SAFE_OFFSET(0)], parts[SAFE_OFFSET(1)]
FROM (SELECT SPLIT(field) parts FROM UNNEST(["1/2", "10/20"]) field)

Upvotes: 0

Related Questions