The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Convert Comma Separated Values To Each Separate Row with v_txtindex.StringTokenizerDelim

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
0
Answers
this means that
list_value
in your example must be aLONG VARCHAR
Try:
SELECT list_value, v_txtindex.StringTokenizerDelim(list_value::VARCHAR(65000) OVER(PARTITION BEST)
FROM tmp_ist_val;`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
thanks, Ariel and marcothesane!!
The issue got resolve now!