CODEWITHSUNDEEP
snowflake-cloud-data-platform
Theo23
Theo23

Reputation: 21

Snowflake - Read comma delimited payload with commas between double quotes?

How to read a comma delimited payload in Snowflake, which can have commas between double quotes in some columns? For instance: (o,"sadasdasd",123123123,"this is an example, of data field, with commas and backslashes\ which should be read as one unique column",0,...)

So far, I am using:

with your_table as 
(select json_text:message::string as payload from table)
split_part(payload,',',1) as firstfield, 
...
from 
your_table

Upvotes: 2

Views: 852

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11046

Here's a JavaScript UDF that can do it:

create or replace function SPLIT_QUOTED_STRING(STR string)
returns array
language javascript
as
$$
var arr = STR.match(/(".*?"|[^",\s]+)(?=\s*,|\s*$)/g);
for (var i = 0; i < arr.length; i++) {
    arr[i] = arr[i].replace(/['"]+/g, '')
}
return arr;
$$;

select split_quoted_string('o,"sadasdasd",123123123,"asdasdasd.www.org,123123,link.com",0');

-- To get a member of the array:
select split_quoted_string('o,"sadasdasd",123123123,"asdasdasd.www.org,123123,link.com",0')[1]::string;

If you want to split into a table instead of an array, use the FLATTEN table function:

select "VALUE"::string as MY_VALUE
from table(flatten (split_quoted_string('o,"sadasdasd",123123123,"asdasdasd.www.org,123123,link.com",0')));

Upvotes: 1

Related Questions