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;