Difficulty creating equivalent of regexp_split_to_array function in Dremio

1

Hello Dremio community,

I am currently working on migrating some SQL code from PostgreSQL to Dremio and encountered an issue with a user-defined function. In PostgreSQL, I have a function called regexp_split_to_array that splits a string into an array of substrings based on a regular expression pattern.

Here is the code I am using to achieve the result in Dremio:

CREATE OR REPLACE FUNCTION pg_regexp_split_to_array(str VARCHAR, match_str VARCHAR) RETURNS VARCHAR RETURN SELECT REGEXP_SPLIT(str, match_str, 'ALL', 1000000000)

I know that I should not be using the VARCHAR as return datatype, but LIST is not working either.

Any insights, suggestions, or alternative approaches would be greatly appreciated.

Thank you in advance!

2

@balaji.ramaswamy can you please help

3

@Maziha Try returning ARRAY<VARCHAR> instead:

CREATE OR REPLACE FUNCTION pg_regexp_split_to_array(str VARCHAR, match_str VARCHAR) RETURNS ARRAY<VARCHAR> RETURN SELECT REGEXP_SPLIT(str, match_str, 'ALL', 1000000000);
2 Likes
4

Thank you, it worked !