CODEWITHSUNDEEP
google-bigquery
Gil Adirim
Gil Adirim

Reputation: 1924

bigquery split string to chars

Suppose I have a table, in which one of the columns is a string:

id    |    value
________________
1     |    HELLO
----------------
2     |      BYE

How would I split each STRING into it's chars, to create the following table:

id    |    value
________________
1     |     H
----------------
1     |     E
----------------
1     |     L
----------------
1     |     L
....

?

Upvotes: 2

Views: 1748

Answers (2)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 13994

You can use SPLIT function with empty string as delimiter, i.e.

SELECT id, SPLIT(value, '') value FROM Table

Please note, that SPLIT returns repeated field, and if you want flat results (wasn't clear from your question), you would use

SELECT * FROM 
  FLATTEN((SELECT id, SPLIT(value, '') value FROM Table), value)

Upvotes: 3

Gil Adirim
Gil Adirim

Reputation: 1924

Apparently, if you pass an empty delimiter, it works:

select id, split(str, '')
from (
  select 1 as id, "HELLO" as str
)

Upvotes: 3

Related Questions