Issues Splitting values separated by delimiters and creating columns for each split in snowflake

Question

I am new to snowflake and I am trying to run an sql query that splits values with delimiters(semi-column) and create columns for each of them.

Table name: lexa

ID	Value
001	2021-02-13 18:17:43;83.89.250.196;10.10.11.29
002	2021-02-13 17:47:56;5.33.18.24;10.10.11.28

what I am trying to achieve

ID	register	Ip1	IP2
001	2021-02-13 18:17:43	83.89.250.196	10.10.11.29
002	2021-02-13 17:47:56	5.33.18.24	10.10.11.28

Answer

Snowflake split function can split a string into an array.

SELECT ID,
       split(value, ';')[0] as register,
       split(value, ';')[1] as IP1,
       split(value, ';')[2] as IP2
  FROM data;

Accepted Answer

Snowflake split function can split a string into an array.SELECT ID,       split(value, ';')[0] as register,       split(value, ';')[1] as IP1,       split(value, ';')[2] as IP2  FROM data;

Advertisement

Answer