Trino implement a function like regexp_split_to_table()?

Question

Everyone,

I am new to Trino, and I find no function in Trino like regexp_split_to_table() in GreenPlum or PostgreSQL. How can I approach that?

select regexp_split_to_table( sensor_type, E',+' ) as type from hydrology.device_info;

Answer 1

There is regexp_split(string, pattern) function, returns array, you can unnest it.

Demo:

select s.str as original_str, u.str as exploded_value
from
(select 'one,two,,,three' as str)s
cross join unnest(regexp_split(s.str,',+')) as u(str)

Result:

original_str     exploded_value 
one,two,,,three  one
one,two,,,three  two
one,two,,,three  three