Your secret weapon for parsing delimited fields. SPLIT and SPLIT_PART are Snowflake functions that make it a snap to get individual components out of a delimited field.
Snowflake Documentation:
SPLIT
https://docs.snowflake.com/en/sql-reference/functions/split
SPLIT_PART
https://docs.snowflake.com/en/sql-reference/functions/split_part