Parse string into elements, where each element will get column with unique name, Bigquery sql-CodePudding

I'm not getting anywhere with chatgpt :)

Big query sql syntax.

Let's say I have a string of IPs separated by commas. Strings can have different lengths.

These are the strings:

First example:

'1.1.1.1, 12.12.12.12'

Second example:

'1.1.1.1, 12.12.12.12, 3.3.3.3'

Using the comma, I want to parse the string.

As a result, I would like each element to have a column name: ip_ its position in the original string.

First example:

ip_1, ip_2,

1.1.1.1, 12.12.12.12'

Second example:

ip_1, ip_2, ip_3

1.1.1.1, 12.12.12.12, 3.3.3.3

Could you please assist me with this query?

Thanks!

CodePudding user response：

Consider below approach

select * from (
  select list, offset, ip
  from your_table, unnest(split(list)) ip with offset
)
pivot (any_value(trim(ip)) as ip for offset   1 in (1,2,3))

if applied to sample data in your question - output is

You can refactor above into dynamic pivot - there are plenty posts here on SO showing the technique