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; $$
Advertisement
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;