Convert Comma Separated Values To Each Separate Row with v_txtindex.StringTokenizerDelim

davds

our current vertica version: Vertica Analytic Database v9.2.1-28

I tried to use v_txtindex.StringTokenizerDelim function to parse list to row.

--this works
select v_txtindex.StringTokenizerDelim('1,2,3,4,5,19 ',',') over () from dual

--this not works

select list_value,v_txtindex.StringTokenizerDelim(list_value,',') over ()
from tmp_list_val
where id = '12345';

And got error: ERROR: Function v_txtindex.StringTokenizerDelim(long varchar, unknown) does not exist, or permission is denied for v_txtindex.StringTokenizerDelim(long varchar, unknown)

please advice

thanks
David

marcothesane

this means that list_value in your example must be a LONG VARCHAR

Try:
SELECT list_value, v_txtindex.StringTokenizerDelim(list_value::VARCHAR(65000) OVER(PARTITION BEST) FROM tmp_ist_val;`

Ariel_Cary

As of Vertica v12.0.2, v_txtindex.StringTokenizerDelim supports long varchar arguments. Meanwhile, you could use the varchar workaround if you don't mind truncating your data.
There was a similar discussion here:
https://forum.vertica.com/discussion/comment/248204#Comment_248204

davds

thanks, Ariel and marcothesane!!

The issue got resolve now!