Home > Software design >  Issues Splitting values separated by delimiters and creating columns for each split in snowflake
Issues Splitting values separated by delimiters and creating columns for each split in snowflake

Time:02-12

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

CodePudding user response:

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;
  • Related