Home > Software engineering >  How to split words in a string into rows Big Query
How to split words in a string into rows Big Query

Time:07-20

I have table with a column that contains a series of strings. I want to go over each one of those strings and map every word to row. I have tried a mock example, before trying it in my project

WITH teste AS (
  SELECT SPLIT(context_page_title, " ") AS exemple FROM (
    SELECT "a b c ddd ee  eee  dd" AS context_page_title,
  )
) 
SELECT * FROM teste

But this returns a single line with an array that contains all words. I tried using UNNEST() but it keeps returning an error:

WITH teste AS (
  SELECT SPLIT(context_page_title, " ") AS exemple FROM (
    SELECT "a b c ddd ee  eee  dd" AS context_page_title,
  )
) 
SELECT UNNEST(context_page_title) FROM teste

But it returns :Syntax error: Unexpected keyword UNNEST at [6:8]

CodePudding user response:

You might want to write this:

WITH teste AS (
  SELECT SPLIT(context_page_title, " ") AS exemple FROM (
    SELECT "a b c ddd ee  eee  dd" AS context_page_title,
  )
) 
SELECT e FROM teste, UNNEST(exemple) e;

more simply,

WITH teste AS (
  SELECT "a b c ddd ee  eee  dd" AS context_page_title,
) 
SELECT example FROM teste, UNNEST(SPLIT(context_page_title, " ")) example;
  • Related