CODEWITHSUNDEEP
google-bigquery
tandem
tandem

Reputation: 2238

Split string in bigquery

I've the following string that I would like to split and given in rows.

Example values in my column are:

['10000', '10001', '10002', '10003', '10004']

Using the SPLIT function, I get the following result:

enter image description here

I've two questions:

  1. How do I split it so that I get '10000', instead of ['10000'?
  2. How do I remove the Apostrof ' ?

Response:

enter image description here

Upvotes: 0

Views: 519

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Consider below example

with t as (
  select ['10000', '10001', '10002', '10003', '10004'] col   
)
select cast(item as int64) num
from t, unnest(col) item

Above is assumption that col is array. In case if it is a string - use below

with t as (
  select "['10000', '10001', '10002', '10003', '10004']" col   
)
select cast(trim(item, " '[]") as int64) num
from t, unnest(split(col)) item

Both with output

enter image description here

Upvotes: 1

Related Questions