I’m trying to count the number of unique elements in a list (with delimiter “,”) in Redshift. I’ve looked into using REGEXP_COUNT with PCRE patterns for regex. Testing with the below list (“list,test,test,count”), I’d like to get 3 (list, test, count) but it’s resulting in 0.

select REGEXP_COUNT('list,test,test,count', '(w+?b)+(?!.*1b)', 1, 'ip');

The regex portion seems to be working as intended in regex101 so I don’t understand what’s not working. Is there something I’m doing wrong or an easier way to identify unique elements of a string in SQL redshift? Ideally a one liner instead of many temp tables but open to that as well.