How to separate a value taken from a table row and store it in an array in a snowflake

I need to return an array in the stored procedure that takes a comma separated string value from a table and divides that string based on the comma and stores them in an array.

Example: My table has a Column Tab_Val with row value as "COL1,COL2,COL3,COL4", I need to take this row in my stored procedure and return an array as arr =['COL1','COL2','COL3','COL4']

Here is my attempt:

CREATE OR REPLACE PROCEDURE TAB()
    RETURNS ARRAY
    LANGUAGE JAVASCRIPT
    AS
    $$
    var arr = [];
    var stmt = snowflake.createStatement({sqlText: "Select Tab_Val From TABLE1"});
    var r = stmt.execute(); 
    return arr;
    $$

​x
 
CREATE OR REPLACE PROCEDURE TAB()    RETURNS ARRAY    LANGUAGE JAVASCRIPT    AS    $$    var arr = [];    var stmt = snowflake.createStatement({sqlText: "Select Tab_Val From TABLE1"});    var r = stmt.execute();     return arr;    $$​

Answer

Try this

CREATE OR REPLACE TABLE ARRAY_TABLE(ARRAY_CONTENT VARCHAR);
INSERT INTO ARRAY_TABLE VALUES('APPLE,ORANGE,GRAPES,PEARS');
SELECT SPLIT(ARRAY_CONTENT, ',') as str_array FROM ARRAY_TABLE;

 
CREATE OR REPLACE TABLE ARRAY_TABLE(ARRAY_CONTENT VARCHAR);INSERT INTO ARRAY_TABLE VALUES('APPLE,ORANGE,GRAPES,PEARS');SELECT SPLIT(ARRAY_CONTENT, ',') as str_array FROM ARRAY_TABLE;​

Advertisement

Answer