Split single row value to multiple rows in Snowflake

Question

I have a table where the column data has a combination of values seperated by ';'. I would like to split them into rows for each column value.

Table data

Now I would like to split them into multiple rows for each value like

I have tried using the below SQL statement.

SELECT DISTINCT COL_NAME FROM "DB"."SCHEMA"."TABLE,
LATERAL FLATTEN(INPUT=>SPLIT(COL_NAME,';'))

But the output is not as expected. Attaching the query output below.

Basically the query does nothing to my data.

Answer 1

It could be achieved using SPLIT_TO_TABLE table function:

This table function splits a string (based on a specified delimiter) and flattens the results into rows.

SELECT * 
FROM tab, LATERAL SPLIT_TO_TABLE(column_name, ';')